ordering a group of matches - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

ordering a group of matches

Author

Topic

ttribe
Starting Member

6 Posts

Posted - 2010-11-15 : 18:06:19

I have a table created from a query that identifies matches using the row_number function. This table contains, in part, the following relevant fields:

create table table 1 (match_no int, indexno int, date datetime)

The table is populated, in part, like this:

insert into table1 values ('1','7','4/29/1983')
insert into table1 values ('1','8','5/2/1983')
insert into table1 values ('1','9','5/3/1983')
insert into table1 values ('1','10','5/4/1983')
insert into table1 values ('1','11','5/5/1983')
insert into table1 values ('2','9','5/3/1983')
insert into table1 values ('2','10','5/4/1983')
insert into table1 values ('2','11','5/5/1983')
insert into table1 values ('2','12','5/6/1983')
insert into table1 values ('2','13','5/9/1983')
insert into table1 values ('3','11','5/5/1983')
insert into table1 values ('3','12','5/6/1983')
insert into table1 values ('3','13','5/9/1983')
insert into table1 values ('3','14','5/10/1983')

i want to create a query that pulls the fifth and final match from that table. So for the dataset given, i would expect the following results:

match_no indexno date
1 11 5/5/1983
2 13 5/9/1983

Since match_no 3 only has 4 values instead of 5, i want to exclude it, and i want to exclude any of the prior matches expect for the last one (as identified by the indexno that is the largest).

Any help is greatly appreciated.

singularity
Posting Yak Master

153 Posts

Posted - 2010-11-15 : 18:44:51

[code]
select match_no, indexno, [date]
from
(select match_no, indexno, [date], row_number() over (partition by match_no order by indexno) as rn
from table1) a
where rn = 5
[/code]

ttribe
Starting Member

6 Posts

Posted - 2010-11-16 : 14:39:13

Thank you again. Works great. I was close, just couldn't get over the last little problem with the code...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-11-18 : 07:12:29

can there be more than 5 matches for any id? in that case, which one you need to return?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources