2

suppose you have a data set having 3 columns: ID, user, date.

is it possible to filter the data based on the minimum date even if some of the rows have identical IDs?

sorry if the question is a bit unclear. hopefully the image below will help clear things.

there are two records having ID=1, with different users as well as dates. what i want to retrieve is the record having an ID=1, USER=A, DATE=2013-01-20 because its date is earlier than that of the second record (ID=1, USER=A, DATE=2013-01-21)

i want to achieve the same effect for the three records having an ID=2. the desired record is ID=2,USER=C,DATE=2013-10-20

basically i want to group these records by their IDs and then from that grouping, get the one with the lowest date

data set + intended output

user571099
  • 1,491
  • 6
  • 24
  • 42

3 Answers3

3
SELECT id, user, date
FROM OriginalData od
WHERE date = (SELECT MIN(date) 
              FROM OriginalDate od1 
              WHERE od.id = od1.id)
PM 77-1
  • 12,933
  • 21
  • 68
  • 111
0
Select * from table_name where date in 
( select MIN(date) from table_name)
PM 77-1
  • 12,933
  • 21
  • 68
  • 111
Pratik Shelar
  • 3,154
  • 7
  • 31
  • 51
0

You have tyo use Group By clause on Id attribute.

Use the following syntax

select * from tab1 where (id, date) in (select id, min(date) from tab1 group by(id))
Rajesh Paul
  • 6,793
  • 6
  • 40
  • 57
  • 1
    Will work in MySql only. – PM 77-1 Oct 12 '13 at 03:38
  • Have you tested in other platforms? – Rajesh Paul Oct 12 '13 at 03:39
  • 1
    I do not have to. You will get an error that you have fields (namely, user and date that are neither in `GROUP BY` nor aggregated. Only MySql implements `GROUP BY` extensions. Try SQL Server or Oracle, among others. See http://stackoverflow.com/questions/12863928/sql-group-by-error-not-a-group-by-expression – PM 77-1 Oct 12 '13 at 03:42