CODEWITHSUNDEEP
mysqlsql
jvk
jvk

Reputation: 2211

How to get two max id from two different rows?

I have table customer table, i need get max ids from two different approvers with same request id

enter image description here

How can i implement this scenairo

Expected output.

id request_id approver_id
 2   1           2
 4   1           1

Thanks for your time

Upvotes: 0

Views: 70

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522471

One approach uses a join to a subquery to restrict to only the max rows you want:

SELECT ca1.id, ca1.request_id, ca1.approver_id
FROM customer_approved ca1
INNER JOIN
(
    SELECT approver_id, MAX(id) AS max_id
    FROM customer_approved
    GROUP BY approver_id
) ca2
    ON ca1.approver_id = ca2.approver_id AND
       ca1.id = ca2.max_id;

screen capture of demo below

Demo

If you are using MySQL 8+, then you may also use ROW_NUMBER here:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY approver_id ORDER BY id DESC) rn
    FROM customer_approved
)

SELECT id, request_id, approver_id
FROM cte
WHERE rn = 1;

Demo

Upvotes: 1

mkRabbani
mkRabbani

Reputation: 16908

You can apply GROUP BY on your columns request_id & approver_id and then select MAX(id) to get your expected output as below-

SELECT MAX(id),request_id ,approver_id
FROM customer_approved
GROUP BY request_id,approver_id

Upvotes: 2

Related Questions