CODEWITHSUNDEEP
sqloracle-databasegreatest-n-per-groupin-clauseora-00600
Sunrunner
Sunrunner

Reputation: 15

Oracle SQL IN-Clause throws Error with Subselect

so here's my Problem: I got this SQL-Statement 

SELECT a.ID,a.CONTENTOF 
FROM MLMDATA.PV_STORAGE a
WHERE 1=1  
AND ID IN (113312,114583,114581,113472,114585,114580,113314)
AND a.SEQ = (SELECT MAX(b.SEQ) FROM MLMDATA.PV_STORAGE b where a.ID = b.ID)

But my Problem is now, that I'm getting an Error:

ORA-00600: Internal Error Code, Arguments: [qernsRowP], [1], [], [], [], [], [], [], [], [], [], []

I can't seem to find a way to select these ID's where only the highest SEQUENZE is selected.... I already created a View, showing only the highest SEQ, but that doesn't work eighter... I'm kinda frustrated, because as far as I know that SQL Worked before and suddenly doesn't work.

Anyone got an Idea on what the Problem could be?

Upvotes: 1

Views: 306

Answers (2)

Md. Suman Kabir
Md. Suman Kabir

Reputation: 5453

Please try this query :

SELECT a.ID,a.CONTENTOF FROM MLMDATA.PV_STORAGE a
INNER JOIN
    (SELECT ID, CONTENTOF, MAX(SEQ) AS SEQ FROM MLMDATA.PV_STORAGE 
    where ID IN (113312,114583,114581,113472,114585,114580,113314)
    GROUP BY ID, CONTENTOF) b 
ON a.id = b.id AND a.SEQ = b.SEQ

Upvotes: 0

user330315
user330315

Reputation:

Try a window function:

select id, contentof
from (
  select id, contentof, 
         row_number() over (partition by id order by seq desc) as rn
  FROM MLMDATA.PV_STORAGE a
  where id IN (113312,114583,114581,113472,114585,114580,113314)
) 
where rn = 1;

Upvotes: 0

Related Questions