CODEWITHSUNDEEP
sqlsql-servert-sql
remh
remh

Reputation: 189

Max and min dates and times T-SQL

I have a database that is setup so that there is an hour column, and a time column. I am trying to grab the earliest time and date. Both the time and date are formatted as decimals(I know not the best...). For example, I tried,

select min(a.date), min(a.time), a.uniqueID
from someTable a
group by a.uniqueID

but this returns the lowest time, even if the time isn't on that date. For example if I have September first at 13.00, and September Second at 8.00, I'll get back September first at 8.00.

Upvotes: 0

Views: 70

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

Did you try this?

select a.date, min(a.time)
from someTable a
group by a.date;

Or if you want the complete row:

select a.*
from someTable a
where a.time = (select min(a2.time) from someTable a2 where a2.date = a.date);

Upvotes: 0

maddy
maddy

Reputation: 50

Try this:-

select min(a.date),min(a.time),a.uniqeID
from somaTable a
inner join someTable b
    on a.date = b.date and a.uniqueID = b.uniqueID
group by a.uniqeID

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133360

You should use a subquery for min date and the join for the min time

select  t.min_date, min(a.time), a.uniqueID 
from someTable a
INNER JOIN (
  select min(a.date) min_date, a.uniqueID
  from someTable a
  group by a.uniqueID
) t ON t.min_date  = a.date and t.uniqueID = a.uniqueID
group by a.uniqueID, t.min_date

Upvotes: 2

Related Questions