asked on

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

slightwv (䄆 Netminder)

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

Open in new window

Jim Horn

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

Open in new window

corey gashlin

ASKER

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 :-)

slightwv (䄆 Netminder)

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

slightwv (䄆 Netminder)

From the sample data provided, post the expected results.

corey gashlin

ASKER

I have posted my expected results above

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.