包含IN子句和GROUP BY CLAUSE的SQL查询 [英] SQL Query Containing IN Clause and GROUP BY CLAUSE

问题描述

我想要一些能够得到如下数据的查询

 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 

数据由ForeignId组成，只选择所有常见ForeignId的最新值。



如果可能使用IN子句和GROUP BY子句只选择最新值。



先谢谢

解决方案

尝试：

  SELECT  *  FROM  MyTable m 
  JOIN （ SE LECT  MAX（Id） AS  MaxId  FROM  MyTable  GROUP   BY  ForeignId）j 
  ON  m.Id = j.MaxId 

另一种选择是使用排名功能 [ ^ ]。在这种情况下， ROW_NUMBER（） [ ^ ]。

  SELECT  * 
  FROM （
  SELECT  *，ROW_NUMBER（） OVER （ PARTITION   BY  ForeignId  ORDER   BY  Val1  DESC  ） AS  RowNo 
  FROM  YourTableName 
） AS  A 
  WHERE  A.RowNo =  1  

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

解决方案

Try:

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

Another option is to use ranking functions[^]. In this case ROW_NUMBER()[^].

SELECT *
FROM (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY ForeignId ORDER BY Val1 DESC) AS RowNo
    FROM YourTableName
) AS A
WHERE A.RowNo = 1

这篇关于包含IN子句和GROUP BY CLAUSE的SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！