Mysql - If there are duplicates in table, select the oldest one according to date_created column

Question

I have table which stores lecturers' available dates for lectures. Structure goes something like this:

+--------------+--------------+------+-----+---------+----------------+
| Field        | Type         | Null | Key | Default | Extra          |
+--------------+--------------+------+-----+---------+----------------+
| id           | int(11)      | NO   | PRI | NULL    | auto_increment |
| lecturer_id  | int(11)      | NO   |     | NULL    |                |
| kraj_id      | varchar(500) | YES  |     | NULL    |                |
| okres_id     | varchar(500) | YES  |     | NULL    |                |
| lecture_date | datetime     | NO   |     | NULL    |                |
| date_created | datetime     | NO   |     | NULL    |                |
+--------------+--------------+------+-----+---------+----------------+

There can be rows with duplicate lecture_date. When this happens I need to select the oldest row according to date_created. Have not solved it yet.

EDIT:

sample data:

+----+-------------+---------+---------------------------------------------------+---------------------+---------------------+
| id | lecturer_id | kraj_id | okres_id                                          | lecture_date        | date_created        |
+----+-------------+---------+---------------------------------------------------+---------------------+---------------------+
| 36 |          38 | 1,4,12  | 1,2,3,4,5,6,7,18,19,20,21,22,72,73,74,75,76,77,78 | 2017-12-29 00:00:00 | 2017-12-16 16:20:38 |
| 37 |          38 | 1,4,12  | 1,2,3,4,5,6,7,18,19,20,21,22,72,73,74,75,76,77,78 | 2017-12-30 00:00:00 | 2017-12-16 16:20:40 |
| 38 |          38 | 1,4,12  | 1,2,3,4,5,6,7,18,19,20,21,22,72,73,74,75,76,77,78 | 2017-12-31 00:00:00 | 2017-12-16 16:20:42 |
| 39 |          41 | 7,12    | 33,34,35,36,37,72,73,74,75,76,77,78               | 2017-12-29 00:00:00 | 2017-12-16 16:21:15 |
| 40 |          41 | 7,12    | 33,34,35,36,37,72,73,74,75,76,77,78               | 2017-12-30 00:00:00 | 2017-12-16 16:21:17 |
| 41 |          41 | 7,12    | 33,34,35,36,37,72,73,74,75,76,77,78               | 2017-12-31 00:00:00 | 2017-12-16 16:21:20 |
+----+-------------+---------+---------------------------------------------------+---------------------+---------------------+

kraj_id and okres_id are ids of regions which are covered by lecturer. I am selecting rows by lecturer_id (for example: WHERE lecturer_id IN (38, 41)). If two or more lecturers cover the same regions there is chance that there will be duplicity in lecture_date. In that case I need to select the oldest according to date_created. In above example that would mean selecting rows with id 36, 37, 38.

example data? And besides are many max-per-group answers hier on Stackoverflow.. . https://stackoverflow.com/search?q=mysql+max+per+group or https://stackoverflow.com/search?q=%5Bgreatest-n-per-group%5D+mysql — Raymond Nijland, Dec 16 '17 at 14:07
@RaymondNijland Hi, I edited my question, added some sample data. I tried some magic with HAVING clause but it did not work out. I suspect using subqueries will be needed. I am not sure how these work. — OGM2, Dec 16 '17 at 15:53
Would something like ORDER BY date_created ASC LIMIT 1 be helpful? — tbedner, Dec 17 '17 at 08:43
@tbedner Hi, thanks for suggestion. I tried it out but it returns only one row. Below you can find solution which worked for me. — OGM2, Dec 27 '17 at 11:10

score 4 · Accepted Answer · edited Apr 26 '21 at 17:55

I found this and it worked for me.

I had to make some changes to the query and this is the final version:

SELECT
  id,
  lecture_date,
  lecturer_id,
  date_created 
FROM lecturer_has_dates
WHERE date_created IN (
  SELECT MIN(date_created)
  FROM lecturer_has_dates
  GROUP BY lecture_date
)
AND lecture_date >= NOW()
AND lecturer_id IN (38, 41)
ORDER BY lecture_date DESC;

Thanks for all your suggestions!

Mysql - If there are duplicates in table, select the oldest one according to date_created column

1 Answers1