0

I have a table like this :

course (id not unique) | coursemodule (nvarchar) | time_added (timestamp)

The table register every coursemodule (consider it a child of course) added with a log of the time it was added

So I want to get the oldest coursemodule created for every courses

Something like 

SELECT course, coursemodule, min(time_added)
FROM my_table
GROUP BY course;

But we can do a group by with a nonaggregated column

I also have seen 

SELECT t.course, t.coursemodule, t.time_added
FROM   my_table t
WHERE  time_added=(SELECT MIN(t2.time_added)
          FROM my_table t2
          WHERE t.course = t2.course);

But the subquery return more than one row so it's not usable...

guillaume latour
  • 352
  • 2
  • 18

2 Answers2

0

This way worked for what I wanted : 

SELECT t.course, t.coursemodule, t.time_added
FROM ( SELECT t2.course, min(t2.time_added)
       FROM my_table t2
       GROUP BY t2.course
) tmp
INNER JOIN my_table t ON t.course = tmp.course AND t.time_added = tmp.time_added
guillaume latour
  • 352
  • 2
  • 18
0

To get the oldest coursemodule for each course, you can just GROUP BY both course and course module, e.g.:

SELECT course, coursemodule, min(time_added)
FROM my_table
GROUP BY course, coursemodule;
Darshan Mehta
  • 30,102
  • 11
  • 68
  • 102
  • This wont be right for me because I want only the oldest coursemodule in each course. If I do a group by course,coursemodule I will obtain the oldest coursemodule by coursemodule ... Anyway Strawberry marked my question as duplicate and I found everything I needed there ;) – guillaume latour Jun 12 '17 at 15:18