CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Ulrik
Ulrik

Reputation: 556

Filter on attribute in SELECT-statement

I have a table in SQL SERVER 2008 R2 that looks like this

TABLE(
    [Id] [int],
    [FK_Id] [int],
    [SequenceNumber] [smallint],
    [value] [smallint]
(

Id is a unique Primary Key but there are multiple rows with the same FK_Id and different sequencenumbers. I want to compare the values of the two rows with the same FK_Id and the highest SequenceNumber. Something like

SELECT t1.value - t2.value
From Table t1
INNER JOIN Table t2
On t1.FK_Id = t2.FK_Id

My problem is that I cant figure out how sort out so I only get the two with the highest SequenceNumber for each FK_ID, could it be done inside the SELECT statement or do I need to make it in steps?

Upvotes: 0

Views: 390

Answers (2)

Kenneth M. Nielsen
Kenneth M. Nielsen

Reputation: 487

I would try to use rownumber and a common table expression. Have a look at my code under here.

with mydata (id, value, row) as
(
SELECT ID, value,
ROW_NUMBER() OVER(PARTITION BY FK_ID ORDER BY Sequencenumber ASC) AS Row
FROM mytable
) select * from mydata where [row] between 1 and 2

Upvotes: 1

user1088172
user1088172

Reputation:

You can use MAX() function 

SELECT t1.value - t2.value
From Table t1
INNER JOIN Table t2
On t1.FK_Id = t2.FK_Id

TO

SELECT T1.Id, (T1.VALUE-T2.VALUE) AS VALUEMIN, MAX(T1.FK_ID) FROM TABLE T1, T2 
WHERE T1.FK_ID = T2.FK_ID
GROUP BY T1.Id

Upvotes: 0

Related Questions