3 or more row values from subset using RowNumber()

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

3 or more row values from subset using RowNumber()

Author

Topic

Buzzard724
Yak Posting Veteran

66 Posts

Posted - 2009-08-21 : 11:20:41

I want to retrieve the top three values from a list of qualifications that someone holds - and populate three columns with the data (SQL2005).

Result set would look something like

Name, Post, QCode1, Qcode2, Qcode3
Peter, NOT teaching, 01, 02, 03

I have tried select statements based on reversing the order of the subset - and also using WITH and RowNumber - as follows

Select Firstname, PostTitle,
(Select Top 1 Qcode from QCode Q where Q.People_id = P.People_ID),
(Select top 1 from (Select Top 2 Qcode from QCode Q where Q.People_id = P.People_ID order by qcode desc))
from JobDetail j
Inner Join People p on p.people_id = j.People_id
Inner Join Post PO on PO.Post_ID = J.JobTitle

Error msg
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'from'.
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'order'.

OR using WITH

Select Firstname, PostTitle,
(Select Top 1 Qcode from QCode Q where Q.People_id = P.People_ID)
;WITH QL AS (Select QCode, ROW_NUMBER() OVER (ORDER BY Q.QCODE) AS ROWNUMBER
from QCode Q where Q.People_ID = P.People_ID)
(Select QCode from QL where RowNumber = 4) AS QCode2,
Lastname
from JobDetail j
Inner Join People p on p.people_id = j.People_id
Inner Join Post PO on PO.Post_ID = J.JobTitle

Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'AS'.

Code to recreate database, tables and data as follows:

CREATE DATABASE Test2 ON
( NAME = Test2_dat,
FILENAME = 'E:\SQL2005\Data\Test2.mdf',
SIZE = 3,
MAXSIZE = 10,
FILEGROWTH = 1 )
LOG ON
( NAME = 'Test1_log',
FILENAME = 'E:\SQL2005\Data\Test2.ldf',
SIZE = 1MB,
MAXSIZE = 5MB,
FILEGROWTH = 1MB )

CREATE TABLE [dbo].[PEOPLE](
[PEOPLE_ID] [uniqueidentifier] NOT NULL,
[LASTNAME] [nvarchar](30) NULL,
[FIRSTNAME] [nvarchar](30) NULL,
[REFERENCENUMBER] [nvarchar](30) NULL,
[FULLNAME] AS ([FIRSTNAME] + ' ' + [LASTNAME]))

CREATE TABLE [dbo].[JOBDETAIL](
[JOBDETAIL_ID] [uniqueidentifier] NOT NULL,
[JOBTITLE] [uniqueidentifier] NULL,
[PEOPLE_ID] [uniqueidentifier] NOT NULL,
[CURRENTRECORD] [nvarchar](5) NOT NULL)

CREATE TABLE [dbo].[POST](
[POST_ID] [uniqueidentifier] NOT NULL,
[POSTTITLE] [nvarchar] (40) NOT NULL)

CREATE TABLE [dbo].[QCode](
[QCode_ID] [uniqueidentifier] NOT NULL,
[QCode] [nvarchar] (5) NOT NULL,
[People_ID] [uniqueidentifier] NOT NULL)

INSERT RECORDS:
--Create one Person Record
Insert into People (People_id, LastName, FirstName)
VALUES (newid(), 'Peter', 'Charles')
Insert into People (People_id, LastName, FirstName)
VALUES (newid(), 'John', 'Test')
--Select * from People

--Create 2 post records
Insert into Post (Post_ID, POSTTITLE)
VALUES (newid(), 'Teaching')
Insert into Post (Post_ID, POSTTITLE)
VALUES (newid(), 'NOT Teaching')
--select * from Post

--Create JobDetail records
Insert into JobDetail (JobDetail_id, People_ID, CurrentRecord)
Select newid(), P.People_ID, 'Yes'
from People P
--Update Post Values in Jobdetail table
Update JobDetail Set JobTitle = PO.POst_ID
from Post PO where PO.POSTTITLE = 'Teaching'

--Create more Jobdetail records
Insert into JobDetail (JobDetail_id, People_ID, CurrentRecord)
Select newid(), P.People_ID, 'Yes'
from People P
--Update Post Values in Jobdetail table
Update JobDetail Set JobTitle = PO.POst_ID
from Post PO where PO.POSTTITLE = 'NOT Teaching'
AND jobdetail.JobTitle is null
--Select * from jobdetail

--Create Q Code Records for one person
Insert into QCode (QCode_ID, QCode, People_ID)
Select top 1 newid(), '01', People_id from People order by lastname desc
Insert into QCode (QCode_ID, QCode, People_ID)
Select top 1 newid(), '02', People_id from People order by lastname desc
Insert into QCode (QCode_ID, QCode, People_ID)
Select top 1 newid(), '03', People_id from People order by lastname desc
Insert into QCode (QCode_ID, QCode, People_ID)
Select top 1 newid(), '04', People_id from People order by lastname desc

thank you for your time and thoughts

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-21 : 12:18:19

Here's one way:


Select Firstname
       ,PostTitle
       ,qCode1 = max(case when ca.rn = 1 then qCode end)
       ,qCode2 = max(case when ca.rn = 2 then qCode end)
       ,qCode3 = max(case when ca.rn = 3 then qCode end)
from JobDetail j
Inner Join People p on p.people_id = j.People_id
Inner Join Post PO on PO.Post_ID = J.JobTitle
cross apply(
       select qCode, row_number() over (order by qCode) rn
       from   qCode
       where  people_id = p.people_id
       ) ca
group by Firstname
       ,PostTitle

Be One with the Optimizer
TG

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2009-08-21 : 12:41:11

Something like this?

WITH top_level (People_Id, Qcode1)
AS (
SELECT TOP 1 People_id,QCode
FROM dbo.QCode
ORDER BY QCode
),
second_level (People_Id, Qcode2)
AS (
SELECT TOP 1 Q.People_id,Q.QCode
FROM dbo.QCode Q
JOIN Top_level X ON Q.People_id = X.People_id AND Q.qcode <> X.qcode1
ORDER BY Q.QCode
),
Third_level (People_Id, Qcode3)
AS (
SELECT TOP 1 Q.People_id,Q.QCode
FROM dbo.QCode Q
JOIN Top_level X ON Q.People_id = X.People_id AND Q.qcode <> X.qcode1
JOIN Second_level Y ON Q.People_id = Y.People_id AND Q.qcode <> Y.qcode2
ORDER BY Q.QCode
)

SELECT A.people_ID, P.Lastname, A.qcode1,B.qcode2,C.qcode3

FROM top_level A
JOIN dbo.PEOPLE P ON A.people_id = P.people_id
LEFT JOIN Second_level B ON A.People_id = B.people_id
LEFT JOIN third_level C ON A.People_id = C.people_id

An infinite universe is the ultimate cartesian product.

Buzzard724
Yak Posting Veteran

66 Posts

Posted - 2009-08-21 : 14:39:34

Impressed - thank you - however when I now apply this to real life situation the output I am looking for is more complex
In other words - I want every person listed and I want either nulls or as many values as there are in the QCode table
e.g.
A.people_ID, P.Lastname, A.qcode1,B.qcode2,C.qcode3, FirstName
xxxxxxx, Peter 01, 02, 03, Charles
yyyyyyy, John Null, Null, Null Test
zzzzzzz, Mary, 01, Null, Null, Jones

I have adjusted the WITH statement to make People the first table - but the results come out as
NULL Peter NULL NULL NULL Charles
A73F4A1B-95BC-4AB0-8EE6-5CDBC335D70E John NULL NULL NULL Test

so with People at the top with left joins and tests for Null to allow for people who have no QCode records query that does not work is as follows

WITH top_level (People_Id, Qcode1)
AS (
SELECT TOP 1 P.People_id, Q.QCode
FROM People P Left OUTER Join QCode Q
on Q.People_id = P.People_ID
ORDER BY Q.QCode
),
second_level (People_Id, Qcode2)
AS (
SELECT TOP 1 P.People_id, Q.QCode
FROM People P
Left outer join Qcode Q on Q.People_id = P.people_id
LEFT JOIN Top_level X ON P.People_id = X.People_id
AND (Q.qcode <> X.qcode1 OR X.QCODE1 IS NULL)
ORDER BY Q.QCode
),
Third_level (People_Id, Qcode3)
AS (
SELECT TOP 1 P.People_id,Q.QCode
FROM People p
Left join Qcode Q on Q.People_id = P.People_id
LEFT JOIN Top_level X ON P.People_id = X.People_id
AND (Q.qcode <> X.qcode1 OR X.QCODE1 IS NULL)
LEFT JOIN Second_level Y ON P.People_id = Y.People_id
AND (Q.qcode <> Y.qcode2 OR X.QCODE1 IS NULL)
ORDER BY Q.QCode
)

SELECT P.people_ID, P.Lastname, A.qcode1,B.qcode2,C.qcode3, P.FirstName

FROM People P
Left Join top_level A on A.people_id = P.people_id
LEFT JOIN Second_level B ON A.People_id = B.people_id
LEFT JOIN third_level C ON A.People_id = C.people_id

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-21 : 14:42:37

did you try my solution?

Be One with the Optimizer
TG

Buzzard724
Yak Posting Veteran

66 Posts

Posted - 2009-08-21 : 15:43:18

Yes I did - thank you - the result set returned is
Peter NOT Teaching 01 02 03
Peter Teaching 01 02 03

which is pretty much there - except that I also want to see Nulls for John Test (who has no QCode records) - so I have been working on a third option (please see code below):
This returns correct values up to any number of columns that I want to add. I now have to prove that I can adapt it to show ROWS for each post that a person holds - which is what yours did.
AND thinking about it - I want these results to be contained within, only part of, a much larger query - hence my query is working on nested subqueries

Select P12.firstname, P12.lastname, P12.People_ID,

(Select qcode
from
( Select qcode, row_number() Over (Partition By firstname order by qcode) as ROWID
from qcode q where Q.People_ID = P12.People_ID) Q1
where Q1.ROWID = 1 ) AS QCode1,

(Select qcode
from
( Select qcode, row_number() Over (Partition By firstname order by qcode) as ROWID
from qcode q where Q.People_ID = P12.People_ID) Q2
where Q2.ROWID = 2 ) AS QCode2,

(Select qcode
from
( Select qcode, row_number() Over (Partition By firstname order by qcode) as ROWID
from qcode q where Q.People_ID = P12.People_ID) Q3
where Q3.ROWID = 3 ) AS QCode3,

(Select qcode
from
( Select qcode, row_number() Over (Partition By firstname order by qcode) as ROWID
from qcode q where Q.People_ID = P12.People_ID) Q4
where Q4.ROWID = 4 ) AS QCode4

from people P12

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-21 : 15:45:03

>>except that I also want to see Nulls for John Test (who has no QCode records)
Just change the "Cross Apply" to "Outer Apply"

EDIT:
also added an ORDER BY 1,2

OUTPUT:


Firstname                      PostTitle                                qCode1 qCode2 qCode3
------------------------------ ---------------------------------------- ------ ------ ------
Charles                        NOT Teaching                             01     02     03
Charles                        Teaching                                 01     02     03
Test                           NOT Teaching                             NULL   NULL   NULL
Test                           Teaching                                 NULL   NULL   NULL

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-21 : 16:20:48

Your 3rd option (nested subqueries) will be very inefficient because of independent scans through qCode each with their own row_number function applied.

Be One with the Optimizer
TG

Buzzard724
Yak Posting Veteran

66 Posts

Posted - 2009-08-21 : 16:29:09

Using RowNumber() this is how it works to pick up the records for each Post that the person holds

Select P12.firstname, P12.lastname, P12.People_ID, Po.PostTitle,

(Select qcode
from
( Select qcode, row_number() Over (Partition By firstname order by qcode) as ROWID
from qcode q where Q.People_ID = P12.People_ID) Q1
where Q1.ROWID = 1 ) AS QCode1,

(Select qcode
from
( Select qcode, row_number() Over (Partition By firstname order by qcode) as ROWID
from qcode q where Q.People_ID = P12.People_ID) Q2
where Q2.ROWID = 2 ) AS QCode2,

(Select qcode
from
( Select qcode, row_number() Over (Partition By firstname order by qcode) as ROWID
from qcode q where Q.People_ID = P12.People_ID) Q3
where Q3.ROWID = 3 ) AS QCode3,

(Select qcode
from
( Select qcode, row_number() Over (Partition By firstname order by qcode) as ROWID
from qcode q where Q.People_ID = P12.People_ID) Q4
where Q4.ROWID = 4 ) AS QCode4

from people P12
inner join JobDetail J on J.People_ID = P12.People_ID
inner join Post PO on PO.Post_id = J.JobTitle
Order by P12.LastName, P12.FirstName

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-21 : 16:35:44

Yes, I see that. We get the same results - but look at the scan counts. this will make a big difference with a large table.

My version: 6 scans

Table 'QCode'. Scan count 3, logical reads 3, physical reads 0
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0
Table 'PEOPLE'. Scan count 1, logical reads 1, physical reads 0
Table 'POST'. Scan count 1, logical reads 4, physical reads 0
Table 'JOBDETAIL'. Scan count 1, logical reads 1, physical reads 0

your version: 19 scans

Table 'QCode'. Scan count 16, logical reads 16, physical reads 0
Table 'POST'. Scan count 1, logical reads 4, physical reads 0
Table 'JOBDETAIL'. Scan count 1, logical reads 2, physical reads 0
Table 'PEOPLE'. Scan count 1, logical reads 1, physical reads 0

Be One with the Optimizer
TG

Buzzard724
Yak Posting Veteran

66 Posts

Posted - 2009-08-21 : 16:42:42

Hi TG - thank you - as you suggest you query works well and produces the same result set as mine.

I know little about optimisation and will take your word for it - and the point you make is important - I already know that the overall query is slow. Fortunately it is only used once or twice a year.

I think (!) I prefer the RowNumber one because the total query is 32 columns and has many other calculations and sub queries for the other columns - grouping by all the other 31 columns would feel onerous - or can I group by the assigned column name? e.g. one of the other columns has a a subquery as below - will the group by statement accept HighestQualification or will I have to repeat the subquery?

When PO.TeachingPost = 'F' THEN '11'
ELSE (Select top 1 SRQL.SIRCode from PersonalKnowledge pk
inner join KnowledgeLevel KL on KL.KnowledgeLevel_ID = PK.KnowledgeLevel
inner join SIRQualificationLevels SRQL on SRQL.HRnetOrderOfLevel = KL.OrderOfLevel
where pk.people_id = p.people_id
order by KL.OrderOfLevel Desc) END AS HighestQualification

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-21 : 16:52:55

Perhaps you can apply this type of thing to your other calculations. As this example shows, try not to put sub-queries in the SELECT clause. Better to make them derived tables in the FROM clause and correlate the results to the rest of the tables. Notice also, I am using row_number as well so you can add as many qCodes as you wish.


Select p.Firstname
       ,p.lastname
       ,p.people_id
       ,po.PostTitle
       ,q.qCode1
       ,q.qCode2
       ,q.qCode3
from   people p
inner join (
       select p.people_id
              ,qCode1 = max(case when oa.rn = 1 then qCode end)
              ,qCode2 = max(case when oa.rn = 2 then qCode end)
              ,qCode3 = max(case when oa.rn = 3 then qCode end)
       from   People p
       outer apply(
              select qCode, row_number() over (order by qCode) rn
              from   qCode
              where  people_id = p.people_id
              ) oa
       group by p.people_id
       ) q
              on q.people_id = p.people_id
left outer join jobDetail j
       on j.people_id = p.people_id
left outer Join Post PO on PO.Post_ID = J.JobTitle
order by 1,2,3

OUTPUT:
Firstname                      lastname                       people_id                            PostTitle                                qCode1 qCode2 qCode3
------------------------------ ------------------------------ ------------------------------------ ---------------------------------------- ------ ------ ------
Charles                        Peter                          02D41A1C-1FF4-4723-AC99-F3E73143169C Teaching                                 01     02     03
Charles                        Peter                          02D41A1C-1FF4-4723-AC99-F3E73143169C NOT Teaching                             01     02     03
Test                           John                           DAA229A3-585E-4D33-AAC6-4C0DD67DA95B Teaching                                 NULL   NULL   NULL
Test                           John                           DAA229A3-585E-4D33-AAC6-4C0DD67DA95B NOT Teaching                             NULL   NULL   NULL

Be One with the Optimizer
TG

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2009-08-21 : 16:56:41

That's a really nice piece of code, TG.

An infinite universe is the ultimate cartesian product.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-21 : 17:01:42

thanks

Be One with the Optimizer
TG

Buzzard724
Yak Posting Veteran

66 Posts

Posted - 2009-08-21 : 17:48:11

Excellent TG.
I will create version of the view based on derived tables. As you suggest I expect the performance will be at least 3 times faster - and probably much more as there are many columns with similar sub queries.

Thank you for your time and knowledge - I have learnt more than how to solve the problem

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources