CODEWITHSUNDEEP
mysqlsql
Eduardo
Eduardo

Reputation: 7141

Efficiently Select the latest row grouped by a column

I have a table:

QUOTE
| id | value | mar_id | date |

And I am trying to select the latest row for each mar_id (market id). I have managed to achieve what I need from the query below: 

SELECT 
q.*
FROM quote q
WHERE q.date = (
  SELECT MAX(q1.date)
  FROM quote q1
  WHERE q.mar_id = q1.mar_id
)

However I find that the query is incredibly slow (>60s), to the extent my database kills the connection.

I did an EXPLAIN to find out why and got the result:

Explain Plan

I have a composite unique index QUO_UQ on mar_id, date which appears to be getting used.

logically this doesn't seem such a tough query to run, what can I do to do this more efficiently?

Upvotes: 1

Views: 60

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

Your query is fine:

SELECT q.*
FROM quote q
WHERE q.date = (SELECT MAX(q1.date)
                FROM quote q1
                WHERE q.mar_id = q1.mar_id
               );

I recommend an index on quote(mar_id, date). This is probably the fastest method to get your result.

EDIT:

I'm curious if you find that this uses the index:

SELECT q.*
FROM quote q
WHERE q.date = (SELECT q1.date
                FROM quote q1
                WHERE q.mar_id = q1.mar_id
                ORDER BY q1.date DESC
                LIMIT 1
               );

Upvotes: 0

skyflakes
skyflakes

Reputation: 63

select * from (
select mar_id, [date],row_number() over (partition by mar_id order by [date] desc ) as [Rank] from
qoute
group by mar_id, [date]) q where Rank = 1

Upvotes: 0

Strawberry
Strawberry

Reputation: 33935

An example of an uncorrelated subquery 

SELECT x.*
  FROM quote x
  JOIN 
     ( SELECT mar_id
            , MAX(date) date
         FROM quote
        GROUP 
           BY mar_id
     ) y
    ON y.mar_id = x.mar_id
   AND y.date = x.date;

Upvotes: 1

Related Questions