Click here to Skip to main content
15,353,432 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi,

I want some query which is able to get the data as shown below

I/P:-
Id       ForeignId        Val1 
1         1                123
2         1                234
3         1                345
4         2                456
5         2                567


O/P:-
Id       ForeignId        Val1
3        1                345
5        2                567


The data is group by ForeignId's and pick only the latest values of all the common ForeignId's.

If Possible use IN Clause and GROUP BY Clause with picking only the latest values.

Thanks In Advance
Posted
Updated 18-Aug-15 7:52am
v2

Try:
SQL
SELECT * FROM MyTable m
JOIN (SELECT MAX(Id) AS MaxId FROM MyTable GROUP BY ForeignId) j
ON m.Id = j.MaxId
   
Comments
King Fisher 18-Aug-15 15:48pm
   
Nice one,
Maciej Los 18-Aug-15 17:10pm
   
Agree!
King Fisher 19-Aug-15 5:13am
   
how r u sir, :)
Maciej Los 18-Aug-15 17:08pm
   
5ed!
binadi007 20-Aug-15 2:28am
   
Nice Answer.......Thanks A Lot :)
OriginalGriff 20-Aug-15 3:06am
   
You're welcome!
Another option is to use ranking functions[^]. In this case ROW_NUMBER()[^].
SQL
SELECT *
FROM (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY ForeignId ORDER BY Val1 DESC) AS RowNo
    FROM YourTableName
) AS A
WHERE A.RowNo = 1
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900