SELECT statement - select first entry in day 3

[net123]

Hello:

I am trying to create a SELECT statement in which it queries 1 table that has a whole bunch of fields with users and their timestamps during a day. A user can have many timestamps during 1 day. All I would like to pick is the earliest time for a day.

So, let's say my table looks like this:

ID User Time
1 1001 10/1/2003 7:49:00AM
2 1002 10/1/2003 8:00:00AM
3 1002 10/1/2003 8:32:00AM
4 1001 10/1/2003 9:00:00AM
5 1001 10/1/2003 11:00:00AM

So it should return is:

User Time
1001 10/1/2003 7:49:00AM
1002 10/1/2003 8:00:00AM

I tried something like this:

Select User, Time
From TableTime
Where Time = (Select MIN(time) from TableTime)...?

and many other combinations I've tried as well but I can't seem to get it. Your help will be greatly appreciated.

 

[mikeyb540]

Try:

Select [User], [Time]
From TableTime
Where Time = (Select MIN(time) from TableTime)

Without the bracket it thinks its a system object

 

[mikeyb540]

Or... now that I see it
It would be more accurate if you do it like this

Select [User], MIN([Time]) as date_time
From TableTime
group by [user]

 

[JamesLean]

mikeyb540's second query will work if you only have these two columns. But if you have other columns in the row and you want to return all of those as well (but still only for the row with the earliest time) then you can do this:

SELECT user, time, thiscol, thatcol
FROM tabletime t1
WHERE time = (
  SELECT MIN(time) FROM tabletime
  WHERE user = t1.user
)

--James

 

[iker3000]

Hi!

I think the following, more complicated code would be more efficient.

SELECT
Table_1.User,
Time,
....
FROM
Table_1
INNER JOIN
(SELECT
User,
MAX(Time) AS Time
FROM
Table_1
GROUP BY
PRODUCT) AS Max_time_table
ON Max_time_table.User = Table_1.User
AND Max_time_table.Time =
Table_1.Time


Iker

 

[net123]

Thanks everyone for your assistance! Greatly appreciate it!