CODEWITHSUNDEEP
sqloracle-database
avdnowhere
avdnowhere

Reputation: 177

SQL query oracle return more than expected result

I have a query to get some data:

select max(v.SEQUENCENO) as vmaxseq, v.CODE, v.NAMECODE, v.CODENO from smalltbl v join 
(select max(SEQUENCENO) as maxseq, CODE, CODENO from smalltbl group by CODE, CODENO) sm
on sm.CODE = v.CODE and sm.CODE = 'D50451489'
group by v.CODE, v.NAMECODE, v.CODENO;

But when I run, it will return more data than I expected:

SQL1

What I want is only return max of VMAXSEQ on each CODENO, something like this:

SQL2

How do I write query to get those 2 data only?

Thank You!

Upvotes: 0

Views: 290

Answers (4)

vzhilin
vzhilin

Reputation: 313

Try this one

select 
    vmaxseq, CODE, NAMECODE, CODENO
FROM (
    select 
        vmaxseq, CODE, NAMECODE, CODENO,
        FIRST_VALUE(vmaxseq) OVER (ORDER BY vmaxseq DESC) as firstmax   
        from (  
            select 
                max(v.SEQUENCENO) as vmaxseq, 
                v.CODE, 
                v.NAMECODE, 
                v.CODENO,               
            from smalltbl v 
                join (select max(SEQUENCENO) as maxseq, CODE, CODENO from smalltbl group by CODE, CODENO) sm
                    on sm.CODE = v.CODE and sm.CODE = 'D50451489'
        group by v.CODE, v.NAMECODE, v.CODENO
    ) 
) where firstmax = vmaxseq

Upvotes: 0

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28403

May be Something Like this

SELECT T.*
FROM smalltbl T INNER JOIN
     (
      SELECT codeno,Max(SEQUENCENO) As vmaxseq
      FROM smalltbl where CODE = 'D50451489'
      GROUP BY codeno
     ) T1 ON T.SEQUENCENO = T1.vmaxseq AND T.codeno = T1.codeno

Upvotes: 0

turboc
turboc

Reputation: 153

namecode is your problem. Because it varies over your rows and doesn't relate directly to codeno, you're essentially getting a cartesian product.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You can do what you want with analytic functions. I think the query you want is:

select sm.*
from (select sm.*, max(SEQUENCENO) over (partition by codeno) as maxseq
      from smalltbl sm
      where sm.CODE = 'D50451489'
     ) sm
where sequenceno = maxseq;

Upvotes: 1

Related Questions