Pinfred Pinfred - 5 months ago 12
SQL Question

Mysql - how to check id and date order

I have a table with 3 columns :


  1. id (auto incremental)

  2. group_id

  3. date



Theoricaly for a same group_id the date for an id would be greater than the last insered one.

For exemple :


  1. | group5 | 2016-06-28

  2. | group9 | 2016-05-01

  3. | group9 | 2016-05-10

  4. | group5 | 2016-06-29

  5. | group9 | 2016-05-12



So if I select group5 it would be :


  1. | group5 | 2016-06-28

  2. | group5 | 2016-06-29



But I had issue in insered date, and I would like to filer only rows where
date and id for a same group aren't ascender.

For exemple :


  1. | group5 | 2016-06-28

  2. | group9 | 2016-05-01

  3. | group9 | 2016-05-10

  4. | group5 | 2016-06-29

  5. | group9 | 2016-05-09



the issue is that for group9 id 3 is 2016-05-10 and next id (5) has a date lower (2016-05-09)

Is it possible to make an sql to filter that ?
Regards

Answer

try the following:

select 1.* from my_table as 1, my_table as 2 
where 1.group_id = 2.group_id and 1.id > 2.id and 1.date < 2.date
Comments