CODEWITHSUNDEEP
sqlsql-server
leora
leora

Reputation: 196459

sql query to get earliest date

If I have a table with columns id, name, score, date

and I wanted to run a sql query to get the record where id = 2 with the earliest date in the data set.

Can you do this within the query or do you need to loop after the fact?

I want to get all of the fields of that record..

Upvotes: 47

Views: 256335

Answers (5)

Kevin Swann
Kevin Swann

Reputation: 1038

While using TOP or a sub-query both work, I would break the problem into steps:

Find target record

SELECT MIN( date ) AS date, id
FROM myTable
WHERE id = 2
GROUP BY id

Join to get other fields

SELECT mt.id, mt.name, mt.score, mt.date
FROM myTable mt
INNER JOIN
( 
   SELECT MIN( date ) AS date, id
   FROM myTable
   WHERE id = 2
   GROUP BY id
) x ON x.date = mt.date AND x.id = mt.id

While this solution, using derived tables, is longer, it is:

  • Easier to test
  • Self documenting
  • Extendable

It is easier to test as parts of the query can be run standalone.

It is self documenting as the query directly reflects the requirement ie the derived table lists the row where id = 2 with the earliest date.

It is extendable as if another condition is required, this can be easily added to the derived table.

Upvotes: 7

Jakub
Jakub

Reputation: 107

Using "limit" and "top" will not work with all SQL servers (for example with Oracle). You can try a more complex query in pure sql:

select mt1.id, mt1."name", mt1.score, mt1."date" from mytable mt1
where mt1.id=2
and mt1."date"= (select min(mt2."date") from mytable mt2 where mt2.id=2)

Upvotes: 2

Yvo
Yvo

Reputation: 19263

If you just want the date:

SELECT MIN(date) as EarliestDate
FROM YourTable
WHERE id = 2

If you want all of the information:

SELECT TOP 1 id, name, score, date
FROM YourTable
WHERE id = 2
ORDER BY Date

Prevent loops when you can. Loops often lead to cursors, and cursors are almost never necessary and very often really inefficient.

Upvotes: 64

Aaron
Aaron

Reputation: 1072

Try

select * from dataset
where id = 2
order by date limit 1

Been a while since I did sql, so this might need some tweaking.

Upvotes: 3

shahkalpesh
shahkalpesh

Reputation: 33476

SELECT TOP 1 ID, Name, Score, [Date]
FROM myTable
WHERE ID = 2
Order BY [Date]

Upvotes: 8

Related Questions