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:
What I want is only return max of VMAXSEQ on each CODENO, something like this:
How do I write query to get those 2 data only?
Thank You!
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;
SEQUENCENO for each CODENO. I would expect difference values of SEQUENCENO on a given CODENO.namecode is your problem. Because it varies over your rows and doesn't relate directly to codeno, you're essentially getting a cartesian product.
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
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
