CODEWITHSUNDEEP
sqloracle-database
bucs10us
bucs10us

Reputation: 43

Get highest value of Oracle table while using Max

I have the follow sql statement. I'm trying to get the highest version. My query is returning multiple results, although sorted by verison desc. How can I just get the highest one?

SELECT pv.version, pv.vin, pv.policyid, pv.segeffdate, pv.segexpdate, pv.changenum from
    nsa_al.polvehicle pv
WHERE pv.vin = '2GTEC19T011201788'
AND pv.changenum > 0
AND pv.VERSION = (SELECT MAX(PV.VERSION) FROM NSA_AL.POLVERSION)
ORDER BY pv.version DESC

I've tried to use the rownum = 1 but I kept getting missing ")" error.

Thanks

Upvotes: 1

Views: 765

Answers (1)

sgeddes
sgeddes

Reputation: 62831

There are a couple of ways to do this. Here is one with a subquery and ROWNUM:

SELECT * 
FROM (
    SELECT pv.version, pv.vin, pv.policyid, pv.segeffdate, pv.segexpdate, pv.changenum 
    FROM nsa_al.polvehicle pv
    WHERE pv.vin = '2GTEC19T011201788'
        AND pv.changenum > 0
    ORDER BY pv.version DESC
) t
WHERE ROWNUM = 1

This will only return a single record. If you need ties, you can use the analytic function RANK() instead.

SELECT * 
FROM (
    SELECT RANK() OVER (ORDER BY version DESC) rnk, pv.version, pv.vin,  pv.changenum
    FROM pv
    WHERE pv.vin = '2GTEC19T011201788'
        AND pv.changenum > 0
    ORDER BY pv.version DESC
) t
WHERE rnk = 1

If you prefer to use a MAX aggregate, then it's easiest to do that with a common table expression and JOIN the table back on itself. This could yield multiple results if there are multiple records with the same version:

WITH CTE AS (
    SELECT pv.version, pv.vin,  pv.changenum
    FROM pv
    WHERE pv.vin = '2GTEC19T011201788'
        AND pv.changenum > 0
) 
SELECT * 
FROM CTE C
  JOIN (
    SELECT MAX(version) maxVersion 
    FROM CTE) C2 ON C.version = C2.maxVersion

Upvotes: 3

Related Questions