SQL Query Containing IN Clause and GROUP BY CLAUSE

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

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 18-Aug-15 7:51am

binadi007

Updated 18-Aug-15 7:52am

v2

Add a Solution

2 solutions

Add a Solution

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

OriginalGriff · Accepted Answer · 2015-08-18T08:14:00

Solution 1

Try:

SQL

SELECT * FROM MyTable m
JOIN (SELECT MAX(Id) AS MaxId FROM MyTable GROUP BY ForeignId) j
ON m.Id = j.MaxId

Posted 18-Aug-15 8:14am

OriginalGriff

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!

Maciej Los · Accepted Answer · 2015-08-18T11:08:00

Solution 2

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

Posted 18-Aug-15 11:08am

Maciej Los

SQL Query Containing IN Clause and GROUP BY CLAUSE

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

SQL Query Containing IN Clause and GROUP BY CLAUSE

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us