CODEWITHSUNDEEP
sqlgroup-by
Raggaer
Raggaer

Reputation: 3318

SQL Group by ordering results

My table looks like this:

----------------------------------------------------------
| ID | League | Reference | Created_at           | Value |
----------------------------------------------------------
| 1  | Test   | Exa       | 2018-08-05 11:52:30  | 12.00 |
----------------------------------------------------------
| 2  | Test   | Alch      | 2018-08-05 12:52:30  | 9.12  |
----------------------------------------------------------
| 3  | Test   | Chrom     | 2018-08-05 12:50:30  | 6.00  |
----------------------------------------------------------
| 4  | Test   | Chrom     | 2018-08-05 10:50:30  | 2.00  |
----------------------------------------------------------

Every 5 minutes I save a reference value on the table

I want to retrieve values of each reference (latest ones ordering by created_at DESC. Currently on my code I just loop over an array of references [exa, alch, ...] and execute the following query (for each league):

SELECT created_at, league, value, reference
FROM currency
WHERE league = ? AND reference = ?
ORDER BY created_at DESC
LIMIT 1

Basically I execute that query for each reference of each league, resulting in a lot of queries executed, and leading to high response times on my server...

The expected result should be, gather each reference value from X league, but only gather the latest inserted value (created_at DESC), each reference type (exa, alch, ...) will have different created_at values (may or not be equal to other reference types)

I tried using group_by with no success at all since I will only get the first results (older ones) of the table:

SELECT created_at, league, value, reference
FROM currency
WHERE league = ? AND reference IN ('exa', 'alch', ...)
GROUP BY created_at DESC

I assume this group_by approach would be faster since its one query for each league, but I always get older results instead of recently created ones...

Upvotes: 0

Views: 66

Answers (5)

espino316
espino316

Reputation: 452

This query seems more efficient, you can chech the fiddle here: http://sqlfiddle.com/#!9/2b2dfe/8

If still have problems in production, review your indexes.

SELECT
  currency.ID,
  currency.League,
  currency.Reference,
  currency.Created_at,
  currency.Value
 FROM
   currency
JOIN (  
SELECT
  League,
  Reference,
  MAX(Created_at) AS Created_at
 FROM
   currency
 GROUP BY
   League,
   Reference
 ) AS a
 ON
   currency.League = a.League
   AND currency.Reference = a.Reference
   AND currency.Created_at = a.Created_at

Upvotes: 1

IShubh
IShubh

Reputation: 364

Try the following query-:

with cte as
(
select created_at, league, value, reference,ROW_NUMBER() over (partition by league,reference  order by   Created_at desc) rn 
from currency
)select * from cte where rn=1

SQL Server

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You can use subquery with limit clause :

select c.*
from currency c
where id = (select c1.id
            from currency c1
            where c1.league = c.league and
                  c1.Reference = c.Reference -- might be you need this also
            order by c1.created_at desc
            limit 1
           );

Upvotes: 1

jarlh
jarlh

Reputation: 44766

Have a sub-query that returns each league's max created_at value. JOIN with that result:

select c1.*
from currency c1
join (select league, max(created_at) max_created_at
      from currency
      group by league) c2
  on  c1.league = c2.league
  and c1.created_at = c2.max_created_at

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Use filtering:

select c.*
from currency c
where c.created_at = (select max(c2.created_at)
                      from currency c2
                      where c2.league = c.league
                     );

Upvotes: 0

Related Questions