1

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).

Improve this question
2
  • Could you add the right resultset for your demo data? I think it'll help to understand what you want. Commented Apr 11, 2018 at 4:11
  • the right resultset would be: 90003 - 835, 90004 - 570, 90005 - 648, ... the answer of Zohar Peled worked for me with "where rn = 2". But I think your solution would work too, I'll test it later. Thank you all for helping! Commented Apr 11, 2018 at 8:13

3 Answers 3

Reset to default
2

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
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

With rn = 2 it gives me the last but one highest value of ColB for each group, so this could be it! Thank you!
What if you have a nevID value that only exists in a single row?
that would not occur in the query-result but in my data-environment this is fortunately not a problem. (for now.... )
2

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

Improve this answer

6 Comments

A good idea! I've never tried to use ROW_NUMBER in ORDER BY block.
I keep forgetting about with ties... Good point about the where clause as well. +1
This also gives the highest value of ColB. I need the last but one highest, but I don't know how to change this query to get that.
@ajudi last? how do you recognize last if not by highest value? there is no implicit order in relation ...
Sorry if I misunderstand you, my english is not the best. Yes, the last one is the highest but I don't need the highest, I need the one before the highest, for example, for 90003 in ColA it would be 835 in ColB. So I guess one step needed is to get ColB in the right order (is this the implicit order?) in each group and then select the second record of each group.
|
0

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)
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.