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.
| Author |
Topic |
|
Scott
Posting Yak Master
145 Posts |
 Posted - 2010-04-13 : 15:23:47
|
| I have a table with 2 fields - ID and Descript.ID | Descript1 | test11 | test22 | test43 | test33 | test5I would like to return the 3 unique ID's and the first Descript for that ID.1 | test12 | test43 | test3What is going to be the cleanest way to do this? (over (partition ...))Thanks |
|
|
Scott
Posting Yak Master
145 Posts |
Posted - 2010-04-13 : 15:43:30
|
I think I have it:Select ID, Descript from (Select ID, Descript, row_number() over (partition by ID order by ID) as row from table) as awhere row = 1 |
 |
|
|
DBA in the making
Aged Yak Warrior
638 Posts |
Posted - 2010-04-13 : 18:47:23
|
quote:
Originally posted by ScottI would like to return the 3 unique ID's and the first Descript for that ID.
How do you define which is the first? The query you posted does seem to work on the sample data, but it's actually take 1 record at random for each ID, because the order by clause is the same as the Partition By clause. There is no guarantee which will be considered the first record.------------------------------------------------------------------------------------Any and all code contained within this post comes with a 100% money back guarantee. |
|
|
|
bklr
Master Smack Fu Yak Hacker
1693 Posts |
Posted - 2010-04-14 : 02:47:23
|
| can u have a datetime column in ur table and then order by on that field then u can get the first added data for the each id.......... |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-04-14 : 03:41:06
|
quote:
Originally posted by bklr
can u have a datetime column in ur table and then order by on that field then u can get the first added data for the each id..........
or even an identity column value will suffice------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
bklr
Master Smack Fu Yak Hacker
1693 Posts |
Posted - 2010-04-14 : 04:22:05
|
quote:
Originally posted by visakh16
quote:
Originally posted by bklr
can u have a datetime column in ur table and then order by on that field then u can get the first added data for the each id..........
or even an identity column value will suffice------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
ya identity column will sufficient.................. |
|
|
|
|
|
|
Distinct