February 21, 2010 at 10:52 pm
hi,
I have a seq number in table i need to take the max seq number then show the results in sucha fashion that group with max seq number comes first.
[Code]
declare @jobs table (street varchar(100),seq int)
insert into @jobs
select 'B',50 union all
select 'B',40 union all
select 'A',30 union all
select 'C',20 union all
select 'B',15 union all
select 'B',10 union all
select 'A',5
select * from @jobs
[/Code]
Expected output:
street | seq
-------------
B | 50
B | 40
B | 15
B | 10
A | 30
A | 5
C | 20
Thank you.
February 22, 2010 at 2:20 am
select * from @jobs
order by max(seq) over (partition by street) desc, seq desc
Hope this helps
Gianluca
-- Gianluca Sartori
February 22, 2010 at 2:37 am
Thanks it works ! 😛
so exactly the partittion is used in order by .. am i correct?
how will the sorting and grouping takes place in that case.. If you can clear then it will help me understanding the query.
Thanks.
February 22, 2010 at 2:44 am
To understand the query you just have to add the order by column to the select list:
select *, max(seq) over (partition by street) as maxseq
from @jobs
This returns the max sequence, partitioned by street.
How this is used in the order by is straightforward.
Hope this was clear enough, English is not my first language.
Regards
Gianluca
-- Gianluca Sartori
February 22, 2010 at 2:47 am
Thanks again
when i gave this input
B | 50
B | 40
A | 50
C | 20
B | 15
B | 10
A | 5
i got some ungrouped results.
February 22, 2010 at 2:55 am
You probably need to keep the streets together. Try adding street in the order by in 2nd position:
select *
from @jobs
order by max(seq) over (partition by street) desc, street, seq desc
Is this what you're after?
-- Gianluca Sartori
February 22, 2010 at 3:00 am
Thanks again,
yes that works,
but still the flow goes above my head. :crying:
February 22, 2010 at 3:16 am
max(seq) over (partition by street)
returns the maximum sequence number in each street group.
In case of ties between different streets, you order by street.
Inside each street group, you want the highest sequence first, so you order by seq DESC.
Which part is unclear to you?
Try running this, it should help understanding.
select *, max(seq) over (partition by street) as maxseq
from @jobs
order by max(seq) over (partition by street) desc, street, seq desc
street seq maxseq
------ ----------- -----------
A 50 50
A 5 50
B 50 50
B 40 50
B 15 50
B 10 50
C 20 20
-- Gianluca Sartori
February 22, 2010 at 3:22 am
Ok, thanks the steps made it clear now.
I never used over/partition in order by clause , i found it new..
Now i understood.
Thanks again.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply