SQL - Select lowest values with group by and order by?

Question

In my rankings database I have a table named times. I also have another table with authors. The authors have author id's (named ath_id inside the times table).

---

Records saved in times table:

id            ath_id       brand_id       time           date
------------- ------------ -------------- -------------- --------------
65125537      5384729      3              44741          May 8  2014
72073658      4298584      1              1104           Jun 28 2015
86139060      4298584      2              2376           Nov 20 2016
92237079      4298584      1              1115           Jun 24 2017
92237082      4298584      1              1104           Jun 24 2017
93436362      5384729      12             376492         Dec 31 2012

---

What I want to achieve

I'd like to retrieve an ordered list of the times that belong to the author (by the author id). I'd like to order them by brand_id, and I only want the records with the lowest time value.

Also, when there are multiple records with the same brand_id and the same time value, I'd like the list to be ordered by date. So the record with the latest date will be last.

---

What I have

I currently use this query: SELECT * FROM times WHERE ath_id = 4298584 GROUP BY brand_id ASC.

It works great, but it limits records with the same brand_id to 1, and thereby it limits records with the same time, even when multiple records have the lowest time value.

---

To sum it up

So in the case of the example above. When I select all the records with ath_id = 4298584, I'd like to retrieve the following ordered list:

id            ath_id       brand_id       time           date
------------- ------------ -------------- -------------- --------------
72073658      4298584      1              1104           Jun 28 2015
92237082      4298584      1              1104           Jun 24 2017
86139060      4298584      2              2376           Nov 20 2016

---

This is my first time doing a bit more advanced SQL queries. I'm working with Laravel, so giving both a raw SQL solution and a Laravel solution using the Laravel Query Builder wouldn't do any harm.

Answer 1

You could try using a derived table to get the min time for an ath_id and brand_id. Then join it back to your original table to get the rest of the data.

SELECT t.*
FROM times t
JOIN (SELECT ath_id, brand_id, MIN(time) AS time FROM dbo.times GROUP BY ath_id, brand_id) b 
  ON t.ath_id = b.ath_id AND t.brand_id = b.brand_id AND t.time = b.time
WHERE t.ath_id = 4298584
ORDER BY t.brand_id ASC, t.date DESC

Answer 2

This is another way you can do it. Although the output would be similar to SQLChao's answer, but the difference is that the inner query is creating and assigning ranks to the combination of ath_id,brand_id and date followed ordered by time. Then in outer query, you can use a filter to separate the rank 1. So basically you are replicating row_number() function.

You can use rnk=1 to rnk <= n in case you want first n records for your combination. But in you case, SQLChao's answer would be faster.

select t3.id,t3.ath_id,t3.brand_id,t3.time,t3.date
from times1 t3
inner join 
(
    select t1.ath_id,t1.brand_id,t1.date,t1.time,count(*) as rnk
    from times1 t1
    inner join times1 t2
    on t1.ath_id=t2.ath_id
    and t1.brand_id=t2.brand_id
    and t1.date=t2.date
    and t1.time >= t2.time
    where t1.ath_id=4298584
group by t1.ath_id,t1.brand_id,t1.date,t1.time
) t4 
on  t3.ath_id=t4.ath_id
and t3.brand_id=t4.brand_id
and t3.date=t4.date
and t3.time = t4.time
and t4.rnk=1
;