How to choose Top 1 of all records?

[Digitalcandy]

I have a table that has ~20 columns of various types such as boolean, nvarchar, and numeric. Is it possible to select the Top 1 of all records for each key?

Sample:

TravLotID(key) Gold(boolean) Status(Nvarchar)
2 1 today
2 1 today
3 0 tomorrow
3 0 tomorrow
3 0 tomorrow
4 1 next week
4 1 next week





Based on the TravLotID field, I'd like to grab the Top row of all columns, so the TravLotID doesn't duplicate.


TravLotID(key) Gold(boolean) Status(Nvarchar)
2 1 today
3 0 tomorrow
4 1 next week





Thanks.

 

[hmckillop]

If all the other fields are duplicated as in your example just group by all the fields
i.e.
SELECT TravLotID, Gold, Status
FROM MyTable
Group by TravLotID, Gold, Status


"I'm living so far beyond my income that we may almost be said to be living apart

 

[r937]

select TravLotID
     , Gold
     , Status
  from yourtable as foo
 where Status
     = ( select max(Status)
           from yourtable
          where TravLotID = foo.TravLotID )

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[Digitalcandy]

I don't think I can use your method when there are boolean fields involved.