Reputation: 53
Please help me with building up following query: I have a joined table from a selection query:
select nevlista.nevID as ColA, nkhk.nevktghID as ColB, nkhk.ktghelyID as ColC
from nevlista
inner join nkhk on nevlista.nevID = nkhk.nevID
where nevlista.nevID = nkhk.nevID
This gives following result:
ColA ColB ColC
90002 629 6
90003 835 9
90003 875 12
90003 112 12
90004 424 17
90004 570 1
90004 905 17
90005 648 1
90005 649 17
90005 523 17
and so on...
Now, I need from every group of same ID-s in ColA, the last but one highest value from ColB (and the value from ColC). (The number of same ID-s in ColA are different, usually there are 2 to 5 records with the same ID).
Upvotes: 1
Views: 375
Reputation: 82534
The simplest thing to do is a cte with Row_number
partitioned by colA ordered by colB desc:
;With cte as
(
select nevlista.nevID as ColA,
nkhk.nevktghID as ColB,
nkhk.ktghelyID as ColC,
ROW_NUMBER() OVER(PARTITION BY nevlista.nevID ORDER BY nkhk.nevktghID DESC) as rn
from nevlista
inner join nkhk on nevlista.nevID = nkhk.nevID
where nevlista.nevID = nkhk.nevID
)
select ColA, ColB, ColC
FROM CTE
WHERE rn = 2 -- assiming every nevlista.nevID appears at least twice in the cte
Upvotes: 2
Reputation: 10711
Consider using group by
with max
:
select nevlista.nevID as ColA, nkhk.nevktghID as ColB, nkhk.ktghelyID as ColC
from nevlista
join nkhk on nevlista.nevID = nkhk.nevID
join
(
select nevID, max(nevktghID) max_nevktghID
from nkhk
group by nevID
) t1 on nkhk.nevID = t1.nevID and
nkhk.nevktghID = t1.max_nevktghID
It can be considerably faster on SQL Server mainly if you have the following index:
CREATE NONCLUSTERED INDEX ix_nkhk _nevID_nevktghID
ON nkhk (nevID,nevktghID) INCLUDE (ktghelyID)
Upvotes: 0
Reputation: 50173
You could use ranking function row_number()
with ties method
select top(1) with ties nl.nevID as cola, n.nevktghID as colb, n.ktghelyID as colc
from nevlista nl
inner join nkhk n on nl.nevID = n.nevID
order by row_number() over (partition by nl.nevID order by n.nevktghID desc)
And, i suspect there would be no need to use where
clause after joins
Upvotes: 2