CODEWITHSUNDEEP
mysql
Jean-Philippe Rivard Lauzier
Jean-Philippe Rivard Lauzier

Reputation: 43

MySQL group by last entry

My table payment_status have these fields:

Many entries could have the same payment_id... So, I want to get the last status for one payment_id...

I have this request that work but take too much time to load... I would like to have an optimize version to group by payment_id and take the last status.

SELECT pstatus.*
FROM `payment_status` AS pstatus
WHERE pstatus.id = (
   SELECT id
   FROM `payment_status`
   WHERE pstatus.status = '200'
   ORDER BY created DESC
   LIMIT 1
)
GROUP BY pstatus.payment_id
ORDER BY pstatus.payment_id DESC

Upvotes: 3

Views: 5380

Answers (5)

Vincent Wen
Vincent Wen

Reputation: 1852

I suppose you are using MySQL database.

Here I have a solution is fast and readable:

select 
    substring_index(
        group_concat(status order by created desc)
        , ',', 1
    ) as status_latest 
from payment_status 
group by payment_id

I'm quite sure it's fast than others SQL statement, You may try it.

Upvotes: 0

Teena Thomas
Teena Thomas

Reputation: 5239

this should work 

SELECT *
FROM  payment_status
WHERE status = '200'    
ORDER BY created DESC
LIMIT 1

Upvotes: 1

Devart
Devart

Reputation: 121922

Try this query -

SELECT t1.* FROM payment_status t1
  JOIN (SELECT payment_id, MAX(created) max_created
        FROM payment_status
        GROUP BY payment_id
        ) t2
    ON t1.payment_id = t2.payment_id AND t1.created = t2.max_created;

...then add WHERE conditions you need.

Upvotes: 3

jeffery_the_wind
jeffery_the_wind

Reputation: 18188

shouldn't yo ujust be able to do this?:

(assuming that created is the timestamp, so "last" = "most recent")

SELECT pstatus.*
FROM `payment_status` AS pstatus
GROUP BY pstatus.payment_id, pstatus.status
ORDER BY pstatus.payment_id DESC, pstatus.created DESC

each row returns should have the payment_id with the most recent status.

Upvotes: 0

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79929

Try to use JOIN:

SELECT p1.*
FROM payment_status p1
INNER JOIN
(
    SELECT id, MAX(created) MaxCreated
    FROM payment_status 
    WHERE status = '200'
    GROUP BY id
) p2 ON p1.id = p2.id AND p1.created = p2.MaxCreated
ORDER BY p1.payment_id DESC

Upvotes: 1

Related Questions