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 |
|
davidmal
Starting Member
19 Posts |
 Posted - 2007-05-11 : 23:10:08
|
| I can't seem to get this one. I have a table somewhat like the following... dbo.HeadofHouseholdHID(PK) | SID(FK) | Relationship | FirstName | LastName |========================================================|1 | 24 | 3 | John | Smith |2 | 24 | 3 | Suzy | Smith |3 | 44 | 3 | Bill | Fold |4 | 44 | 3 | Ann | Fold |5 | 49 | 3 | Greg | Bath |6 | 49 | 3 | Anita | Bath |7 | 52 | 3 | Step | Parent |8 | 52 | 4 | Biological| Parent |and so on.I there a way to extract a list of HIDs where there is a 'DISTINCT' relationship between SID and Relationship or a 'select top(1)' kind of thing. To make things more complicated, the relation column to SID would have to be distinct to values IN (3,4,or 6). {parent,stepparent or guardian} I only want to extract one record per SID. It doesn't matter if relation is 3,4 or 6.Id like to get the following result set...HID=======1357I could do this with a cursor and a temp table but the query would be very slow and resource intensive. Can this be done in a select statement? Any ideas would be greatly appreciated.DavidMal |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-05-11 : 23:45:05
|
| Select Min(HID) as HID from tablegroup by SIDMadhivananFailing to plan is Planning to fail |
 |
|
|
|
|
|
Extracting first row for foreign key keeping PK