Query to fetch multiple columns with distinct values for one of these

Question

I have a table with the following columns : id, int_value, date, desc . Primary key is (id, int_value, date).

I would like to query the table to get id, int_value and date columns but with distinct id and int_value ordered in desc.

For example, imagine you have the following rows in the table

id | int_value | date | desc
1       150      2016   desccc
2       120      2014   ddd
1       160      2016   aaa
3       180      2015   ccc
2       135      2016   ddd

With my query, I would like to get that :

id | int_value | date | desc
3       180      2015   ccc
1       160      2016   aaa
2       135      2016   ddd

For the moment, I made the following query :

select id, int_value, date from table t where int_value = (select
   max(int_value) from table where t.id = id) order by int_value desc;

It works well but if there are same int_value values for a given id, there will be two rows for the same id.

My question is : can you help me to create a query to avoid this problem ?

Update

It seems the following query do the job :

SELECT id, MAX(int_value) AS score, date FROM table GROUP BY id order by score desc

Thanks for your help.

Sylvain

Possible duplicate of [How can I SELECT rows with MAX(Column value), DISTINCT by another column in SQL?](http://stackoverflow.com/questions/612231/how-can-i-select-rows-with-maxcolumn-value-distinct-by-another-column-in-sql) — Vamsi Prabhala, Jun 02 '16 at 18:22

score 0 · Answer 1 · answered Jun 02 '16 at 19:06

One method is to emulate row_number() using variables:

select id, int_value, date
from (select id, int_value, date,
             (@rn := if(@i = id, @rn + 1,
                        if@i := id, 1, 1)
                       )
             ) as rn
      from table t cross join
           (select @i := 0, @rn := 0) params
      order by id, int_value desc
     ) t
where rn = 1;

score 0 · Answer 2 · answered Jun 02 '16 at 19:48

select a.*, b.[desc] from
 (select id, max(int_value) as int_value, max(date) as date from YourTableName group by id) a 
left join  YourTableName b on (a.id=b.id and a.int_value=b.int_value and a.date=b.date)
order by date

this produces the result you want:

id          int_value   date        desc
----------- ----------- ----------- ------
3           180         2015        ccc
1           160         2016        aaa
2           135         2016        ddd

(3 row(s) affected)

please don't forget to mark this as the correct answer. thanks — Tudor Saru, Jun 02 '16 at 19:48

Query to fetch multiple columns with distinct values for one of these

2 Answers2