January 15, 2008 at 4:05 am
Hi there good people
I wager this is far simpler than my brain is allowing for today!
Im trying to get the `first record found` for each unique id in a set of records
example...
[font="Courier New"]
id, value, ordering, a, b, c
1, "fred", 20, 4, 6, 8
2, "fred", 30, 5, 7, 2
3, "fred", 10, 1, 9, 6
4, "bert", 30, 8, 2, 3
5, "bert", 10, 4, 1, 9
6, "fred", 40, 6, 4, 3
7, "emma", 20, 2, 2, 7
8, "emma", 40, 9, 6, 2
[/font]
The results im after, taking the `value` column as being the unique id I want, would be the first time it appears in my records, ordered by `ordering`
results
[font="Courier New"]
3, "fred", 10, 1, 9, 6
5, "bert", 10, 4, 1, 9
7, "emma", 20, 2, 2, 7[/font]
The a,b,c columns of data could be anything and won't be unique.
At the moment im doing the equivalent of
SELECT * FROM table as t1 WHERE id IN (
SELECT TOP 1 id FROM table as t2 WHERE t2.id = t1.id ORDER BY ordering
)
works - its really really slow. query execution plans are showing 97% of the time are being spent sorting. And it is using indexes.
Any thoughts appreciated!
Many thanks,
Martin
January 15, 2008 at 4:16 am
You can use the row_number function on SQL Server 2005 (I assume you are using this since this is the 2005 forum :))
SELECT *
FROM ( SELECT row_number() OVER ( PARTITION BY value ORDER BY ordering ) nr
, id
, value
, ordering
, a
, b
, c
FROM mytable
) x
WHERE x.nr = 1
Regards,
Andras
January 15, 2008 at 6:37 am
Superb, many thanks for that 😀
Martin (learnt another new thing today)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply