CODEWITHSUNDEEP
mysqlsql
user2712982
user2712982

Reputation:

Filter result based on column value

This might seem pretty simple. But after spending sometime, I thought of sharing this. I have the following query.

SELECT *
FROM UPGRADE_HISTORY
ORDER BY MERCHANT_ID, RELEASE_ID

Release_ID lists all the releases corresponds to a particular merchant. In my case, I need to retrieve the result set corresponding to the latest RELEASE_ID per each Merchant.

How can I achieve this?

Upvotes: 0

Views: 61

Answers (4)

Ayushi
Ayushi

Reputation: 21

select uh.*
from Upgrade_History Uh
where Release_ID>= All( Select Release_ID from Uh2
                        where Uh.Merchant_ID=Uh2.Merchant_ID)

Upvotes: 0

The Shooter
The Shooter

Reputation: 733

Try this query (should work):

SELECT 
    *
FROM UPGRADE_HISTORY UH1
INNER JOIN 
(
    SELECT 
        MERCHANT_ID
        ,MAX(RELEASE_ID) RELEASE_ID
    FROM UPGRADE_HISTORY
    GROUP BY MERCHANT_ID
) UH2 ON 
UH2.MERCHANT_ID = UH1.MERCHANT_ID
AND UH2.RELEASE_ID = UH1.RELEASE_ID

Upvotes: 0

jonju
jonju

Reputation: 2736

The intended query is

SELECT uh.* 
FROM UPGRADE_HISTORY uh
Where uh.RELEASE_ID = (SELECT MAX(RELEASE_ID) 
                       FROM UPGRADE_HISTORY 
                       WHERE MERCHANT_ID=uh.MERCHANT_ID) 
ORDER BY uh.MERCHANT_ID, uh.RELEASE_ID

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271231

I think this is what you want:

select uh.*
from upgrade_history uh
where uh.release_id = (select max(uh2.release_id)
                       from upgrade_history uh2
                       where uh2.merchant_id = uh.merchant_id
                      );

This returns one row per merchant, with the largest value of the release id.

Upvotes: 1

Related Questions