How to filter a sql request with DISTINCT and multiple results

Question

I want to filter the results, by not showing the same SAISON_ID, but to choose the SAISON_ID that have the most recent DATE_START

SELECT saison_id, date_start, date_end FROM saison

This request give me this result :

saison_id   date_start  date_end
0   2018-01-05  2018-01-12
0   2019-01-19  2019-02-05
1   2018-01-15  2018-02-13
2   2018-02-17  2018-03-24
3   2018-03-25  2018-06-12
4   2018-06-13  2018-09-18
5   2018-07-05  2018-11-19
6   2018-08-28  2018-11-20
7   2018-11-21  2019-01-17

If I do that :
SELECT DISTINCT(saison_id), date_start, date_end FROM saison GROUP BY saison_id
I have this new result :

saison_id   date_start  date_end
0   2018-01-05  2018-01-12
1   2018-01-15  2018-02-13
2   2018-02-17  2018-03-24
3   2018-03-25  2018-06-12
4   2018-06-13  2018-09-18
5   2018-07-05  2018-11-19
6   2018-08-28  2018-11-20
7   2018-11-21  2019-01-17

How can I get this instead ? (look the saisonID 0)

saison_id   date_start  date_end
0   2019-01-19  2019-02-05
1   2018-01-15  2018-02-13
2   2018-02-17  2018-03-24
3   2018-03-25  2018-06-12
4   2018-06-13  2018-09-18
5   2018-07-05  2018-11-19
6   2018-08-28  2018-11-20
7   2018-11-21  2019-01-17

i can not understand your question ... what exactly you are looking for — Ritul Lakhtariya, Jan 21 '19 at 11:17
I'm looking to choose between the two saison_id "0" : I want that the DISTINCT choose the one that have the most recent date_start — Via, Jan 21 '19 at 11:19
Have a look at https://stackoverflow.com/questions/3442931/sql-server-select-distinct-rows-using-most-recent-value-only — Vishal Raghavan, Jan 21 '19 at 11:21
I'm not sure to fully understand this one, can you show me an example plz ? — Via, Jan 21 '19 at 11:29
@ritul : Yes right I want to skip the first one, but it's just an exemple, I want to choose only the one with the most recent date_start — Via, Jan 21 '19 at 11:30
you can do somthing like lowest date in sub query and set it on where clouse so it will help you. — Ritul Lakhtariya, Jan 21 '19 at 11:33
[`DISTINCT`](https://dev.mysql.com/doc/refman/5.7/en/select.html) does not apply to only one column but to the entire row. It doesn't work with only one column because it doesn't make any sense. Just to make this clear, `DISTINCTROW` is an alias of `DISTINCT`. — axiac, Jan 21 '19 at 11:37
SELECT DISTINCT(saison_id), date_start, date_end FROM saison ORDER BY date_start DESC not tested. you not using MATH operation so you don't need the group by (i mean max,min,count....) and if i am correct if you set it up by the date_start DESC i will work for you. because it will first order than pick the first but test it out. YOU NEED TO USE FOREIGN KEY BECAUSE IT SHOULD BE A RUNNING SEQUENCE. NO REASON for it to have the same key. — Ido Bleicher, Jan 21 '19 at 12:42

Deepak Kumar · Answer 1 · 2019-01-21T12:13:42.723

Hi use this query to get the desired result

select id,[start],[end] 
from 
(select Row_Number() over(Partition By id order by [start] Desc) as Rownum,*  from @session) a 
where Rownum =1

In case this query is little complex to you then you can use this one also with the same result

select a.id,a.[start],a.[end] from @session a
inner join (select id, max([start]) as mx_s from @session group by id) b
on a.id = b.id and a.[start] = b.mx_s

How to filter a sql request with DISTINCT and multiple results

1 Answers1