CODEWITHSUNDEEP
sqlt-sql
Kasper Hansen
Kasper Hansen

Reputation: 6557

Beginner SQL - Get DISTINCT entries (by column)

I have a table called OnlineSpeakers that logs when (Time) people (SpeakerName) are speaking online. It logs every 5 minutes the people that is currently speaking, so for example Bob would have 

2014-06-06 07:05, Room 205, Bob
2014-06-06 07:00, Room 205, Bob
2014-06-06 06:55, Room 205, Bob
...
2014-06-06 06:00, Room 205, Bob

The table is like this:

OnlineSpeakers

I would like to extract a list of the newest speakers that has been registered. So for Bob it would be 'Bob, 2014-06-06 06:00' that would signal Bob's debut as a speaker.

I know how to get this for Bob:

SELECT TOP (1) Time, SpeakerName
FROM     OnlineSpeakers
WHERE  (SpeakerName = 'Bob')
ORDER BY Time ASC

But I don't know how to get it for each speaker in OnlineSpeakers

SELECT DISTINCT SpeakerName
FROM     OnlineSpeakers

I would like a list like this:

2014-06-06 06:00, Room 205, Bob
2014-06-06 05:00, Room 205, Tim
2014-06-06 03:55, Room 205, George
2014-06-06 06:00, Room 205, Martin
...

I tried replacing the "SpeakerName = 'Bob'" "with a SpeakerName IN" and then a list of unique names, but then it just gives the full list of the table with multiple time for each speaker.

Any help is appreciated.

Upvotes: 0

Views: 39

Answers (1)

Liath
Liath

Reputation: 10191

You need the Group By operator.

select SpeakerName, Min(Time) from OnlineSpeakers
Group by SpeakerName

This will take all your and group them by the name of the speaker, when you do this you will need to find a way to decide which of the range of Times you want to display (this is called an aggregate function). However you've already told us you want the first, therefore you can use the Min function.

Bring me all the OnlineSpeakers, grouped by the SpeakerName and include the first Time with each group.

Upvotes: 2

Related Questions