Distinct with multiple column values?

[twifosp]

I'm trying to get a list of distinct values.

Lets say I have a list of ids.

1
2
3
4

Those ids have other info, specifically timestamps. So if I have multiple events on one of the id, then it gets another timestamp. So what I end up is something like this:

Id timestamp
1 3:04 pm
2 5:03 pm
2 5:05 pm
3 6:00 pm
3 6:30 pm
4 7:00 pm

So how would I go about doing a select distinct(id) where it only grabs the first one?

 

[DavidSL]

Select ID, Min(TimeStamp) From myTable Group By ID

 

[twifosp]

Thank you for the reply... my fault for not specifying that I have additional values that won't be contained in an aggregate function.

I have 11 columns that I have to select...

 

[DavidSL]

Try It:

Select * From myTable
Where TimeStamp In
(Select Min(TimeStamp) From myTable Group By ID)

 

[twifosp]

Ahh, why didn't I think of using an in subquery... well thats why I love this forum, always someone who thinks of what you didn't.

Thanks David!

 

[JamesLean]

That query isn't necessarily going to work properly. What happens if one id has a timestamp that happens to match the MIN(timestamp) of another id? That row will get included wrongly. You need to correlate the subquery like this:

SELECT * FROM mytable t
WHERE timestamp = (
  SELECT MIN(timestamp)
  FROM mytable
  WHERE id = t.id
)

--James