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 |
|
pr4t3ekd
Starting Member
31 Posts |
 Posted - 2009-09-04 : 09:02:24
|
Hey,Just started using SQL server today.I have the following table e.g.FName | LName | IDPeter | Fang | 6000James | Fang | 7000Data | TEST | 6000NULL | NULL | 8000NULL | NULL | 9000NULL | NULL | 10000NULL | NULL | 7000 I would like all the records where ID is distinct or the first time SQL server see's an ID... so really the equivelent to First in Access or something.I tried Distinct ofcourse but this is based on the whole row so therefore not sufficient |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-09-04 : 09:18:24
|
where Id is distinct:select FName,LName,ID from table group by FName,LName,ID having count(*)=1First:Without a datetime you cannot see which ID was the first...
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
bklr
Master Smack Fu Yak Hacker
1693 Posts |
Posted - 2009-09-04 : 09:24:11
|
| use row_number.......... |
|
|
|
pr4t3ekd
Starting Member
31 Posts |
Posted - 2009-09-04 : 09:26:58
|
| so how would i just get a unique list regardless of it being the first id or a latter id. In the end, i just want unique ID'sBased on the above, the result should be:FName | LName | IDPeter | Fang | 6000James | Fang | 7000NULL | NULL | 8000NULL | NULL | 9000NULL | NULL | 10000 |
|
|
|
bklr
Master Smack Fu Yak Hacker
1693 Posts |
Posted - 2009-09-04 : 09:28:56
|
| select * from (select row_number()over(partition by id order by id)as rid, * from tablename ) s where rid = 1 |
|
|
|
pr4t3ekd
Starting Member
31 Posts |
Posted - 2009-09-04 : 09:33:02
|
| thank you bklr. that seems to work. will test that with the real data. |
|
|
|
|
|
|
First Equivalent in SQL Server