CODEWITHSUNDEEP
sqloracle-databasegreatest-n-per-group
spyr0
spyr0

Reputation: 199

Oracle SQL: Select ALL rows by each of their most recent ID column

This is probably very simple to solve but I can't seem to Google it correctly to find the answer.

I am looking to pull all rows from a table by their most recent ID without manually typing every column name. I have this kind of already working with the following query:

SELECT 
 STANDARD_ID,
 MAX(ID)

FROM
 HIST_ILLUM_RT

WHERE STANDARD_ID IS NOT NULL

GROUP BY 
 STANDARD_ID

Which pulls all the rows that I need. I'm kind of trying to get around this by using this query:

SELECT 
*

FROM
 HIST_ILLUM_RT

WHERE ID = (SELECT MAX(ID)
            FROM HIST_ILLUM_RT)

Which fetches all the columns but only one row. E.g Part of the row fetched

The number of rows which should be pulled will change overtime.

Is it possible to pull all the columns as well as the rows without manually typing each column name(In this case there are over 50)? Or is there no choice but to manually type it out in Oracle sql?

Upvotes: 1

Views: 459

Answers (2)

Jaydip Jadhav
Jaydip Jadhav

Reputation: 12309

Try this :

WITH CTE AS(

       SELECT  STANDARD_ID,ID,
               ROW_NUMBER() OVER(Partition BY ID ORDER BY ID DESC) RN
       FROM HIST_ILLUM_RT
       WHERE STANDARD_ID IS NOT NULL
)
SELECT STANDARD_ID,ID
FROM CTE 
WHERE RN = 1;

Upvotes: 1

Lamar
Lamar

Reputation: 1849

How about using your own query with GROUP BY?

SELECT *
FROM HIST_ILLUM_RT
WHERE ID IN (
    SELECT MAX(ID)
    FROM HIST_ILLUM_RT GROUP BY STANDARD_ID
)

Upvotes: 1

Related Questions