We help IT Professionals succeed at work.

Need to only show first row of each "RANK" result

Trying to get only 1st result of each rank item to show.

I am running the following q]ery statement
SELECT teammember, eta, designstartdate, proj_name, id , rank() OVER(PARTITION BY proj_name ORDER BY teammember) rnk
 FROM OCI_SCHEDULER_VIEW2 where eta is not null 

Open in new window


This is giving me the following results -

teammember	eta	designstartdate	proj_name	id	rnk
1119	2020-02-20	2020-02-03	OCI DYNAMICS CRM PROJECT MANAGEMENT SOFTWARE	10286	1
1135	2020-02-20	2020-02-03	OCI DYNAMICS CRM PROJECT MANAGEMENT SOFTWARE	10285	2
1141	2020-02-20	2020-02-03	OCI DYNAMICS CRM PROJECT MANAGEMENT SOFTWARE	17565	3
1141	2020-02-20	2020-02-03	OCI DYNAMICS CRM PROJECT MANAGEMENT SOFTWARE	1020	3
1152	2020-02-20	2020-02-03	OCI DYNAMICS CRM PROJECT MANAGEMENT SOFTWARE	10283	5
182745	2020-02-20	2020-02-03	OCI DYNAMICS CRM PROJECT MANAGEMENT SOFTWARE	10287	6
9999	2020-02-20	2020-02-03	OCI DYNAMICS CRM PROJECT MANAGEMENT SOFTWARE	10284	7
1119	2020-02-07	2020-01-01	TEST GANT	17843	1
1135	2020-02-07	2020-01-01	TEST GANT	17842	2
1141	2020-02-07	2020-01-01	TEST GANT	17846	3
1141	2020-02-07	2020-01-01	TEST GANT	17847	3
1152	2020-02-07	2020-01-01	TEST GANT	17841	5
182745	2020-02-07	2020-01-01	TEST GANT	17844	6
192837470	2020-02-07	2020-01-01	TEST GANT	17845	7

Open in new window


I want to ONLY display the 1st of each set( for instance the Team Member "1141" they should show line 3 and line 10 but NOT line 4 and not line 11. ) They should only show me the line 3 and the line 10 as well as their ID column
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:

Then do you want RANK?  I think ROW_NUMBER would give you what you want, then select where RN=1?


Something like:

 
SELECT teammember, eta, designstartdate, proj_name, id from ( 
	SELECT teammember, eta, designstartdate, proj_name, id , 
		row_number() OVER(PARTITION BY proj_name ORDER BY teammember) rn   
	FROM OCI_SCHEDULER_VIEW2 where eta is not null  
) x 
where rn=1 

Jim HornSQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015

Commented:

Throw it in a subquery and then WHERE rnk = 1

SELECT teammember, eta, designstartdate, proj_name, id, rnk
FROM (
 
   SELECT teammember, eta, designstartdate, proj_name, id , 
    rank() OVER(PARTITION BY proj_name ORDER BY teammember) rnk  
   FROM OCI_SCHEDULER_VIEW2 
   where eta is not null) a
WHERE rnk = 1
 

Author

Commented:
Only issue is this is only returning 2 rows for teammember 1119. I need more than that -


From the posted question -

Below is what I am looking for with results compared to the results in the question -

teammember	eta	designstartdate	proj_name	id	rnk
1119	2/20/2020	2/3/2020	OCI DYNAMICS CRM PROJECT MANAGEMENT SOFTWARE	10286	1
1135	2/20/2020	2/3/2020	OCI DYNAMICS CRM PROJECT MANAGEMENT SOFTWARE	10285	2
1141	2/20/2020	2/3/2020	OCI DYNAMICS CRM PROJECT MANAGEMENT SOFTWARE	17565	3
1152	2/20/2020	2/3/2020	OCI DYNAMICS CRM PROJECT MANAGEMENT SOFTWARE	10283	5
182745	2/20/2020	2/3/2020	OCI DYNAMICS CRM PROJECT MANAGEMENT SOFTWARE	10287	6
9999	2/20/2020	2/3/2020	OCI DYNAMICS CRM PROJECT MANAGEMENT SOFTWARE	10284	7
1119	2/7/2020	1/1/2020	TEST GANT	17843	1
1135	2/7/2020	1/1/2020	TEST GANT	17842	2
1141	2/7/2020	1/1/2020	TEST GANT	17846	3
1152	2/7/2020	1/1/2020	TEST GANT	17841	5
182745	2/7/2020	1/1/2020	TEST GANT	17844	6
192837470	2/7/2020	1/1/2020	TEST GANT	17845	7

Open in new window



So notice that all the records are still there except for the one that was "TeamMember" 1141 they are supposed to only show me 1 for that particular project name..

I may be confusing on this :-)
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:

Then do you need to also partition by teammember in the window function?

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:

From the sample data provided, post the expected results.

Author

Commented:
I have posted my expected results above
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:

Try this (I changed the table name for my test):

 
SELECT teammember, eta, designstartdate, proj_name, id from ( 
	SELECT teammember, eta, designstartdate, proj_name, id , 
		row_number() OVER(PARTITION BY teammember,proj_name ORDER BY teammember) rn   
	FROM junk where eta is not null  
) x 
where rn=1 
order by proj_name,teammember

My test is here:

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=fb61757eaf44cd623dda2719a6822b86

Author

Commented:
Thanks Alot - This was amazing!

Explore More ContentExplore courses, solutions, and other research materials related to this topic.