How to use rank over this query
-
Hi All,
Any help with this query is really appreciated.
create table test(stageid int,stagestatustypeid int, applictaionid int)
insert test values(7, 1, 10)
insert test values(77, 2, 10)
insert test values(102, 2,10)
insert test values(27, 1, 23)
insert test values (35, 2, 54)
insert test values(67, 2, 54)
insert test values(98, 2, 54)
insert test values (39, 2, 39)
insert test values(61, 2, 39)
insert test values(90, 1, 39)
I want to select max(stageid) for an applicationid where stagestatustypeid <> 1
and also the stageid where there is only one stageid for an applictaion whose stagestatustypeid = 1
For applicationid 10 I want to return stageid as 102
for application 23 I want to return stageid as 27
for applicationid 54 I wnat to return stageid as 98
for applicationid 39 I want to return stageid as 61
Thanks in advance
-
with cte as (
select stageid,stagestatustypeid, applictaionid,
row_number() over(partition by applictaionid order by case when stagestatustypeid<>1 then 0 else 1 end,stageid desc) as rn
from test)
select applictaionid,stageid
from cte
where rn=1
order by applictaionid
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Mark-101232 (3/30/2012)
with cte as (
select stageid,stagestatustypeid, applictaionid,
row_number() over(partition by applictaionid order by case when stagestatustypeid<>1 then 0 else 1 end,stageid desc) as rn
from test)
select applictaionid,stageid
from cte
where rn=1
order by applictaionid
Hi, Mark.
That's an interesting technique. Could you please explain how the "ORDER BY 0" works?
I thought the order columns started at 1. At least, "0" is not working for me in a regular SELECT.
Thank you.
Best regards,
Andre Guerreiro Neto
Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA -
codebyo (3/30/2012)
Mark-101232 (3/30/2012)
with cte as (
select stageid,stagestatustypeid, applictaionid,
row_number() over(partition by applictaionid order by case when stagestatustypeid<>1 then 0 else 1 end,stageid desc) as rn
from test)
select applictaionid,stageid
from cte
where rn=1
order by applictaionid
Hi, Mark.
That's an interesting technique. Could you please explain how the "ORDER BY 0" works?
I thought the order columns started at 1. At least, "0" is not working for me in a regular SELECT.
Thank you.
This is simply to order rows such that all non type 1 stagestatustypeid are before all type 1.
Try running the query below to see if it makes things any clearer
select stageid,stagestatustypeid, applictaionid,case when stagestatustypeid<>1 then 0 else 1 end as X
from test
order by X
The 0 and 1 are arbitrary and could equally be
case when stagestatustypeid<>1 then 100 else 99999 end as X
or
case when stagestatustypeid<>1 then 'A' else 'Z' end as X
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
Thanks a lot for the detailed explanation!
I got confused thinking that ORDER BY 0 or 1 was about the column order like in:
SELECT TOP(10) name, type FROM sys.objects ORDER BY 1;
But it's actually ordering an int value not the column position.
Thank you. :blush:
Best regards,
Andre Guerreiro Neto
Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA -
Thanks Mark your solution worked.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply