按查询查找组中的最后一行-SQL Server [英] Find last row in group by query-SQL Server

问题描述

我在SQL Server中有表.我想在每个组中找到最后一行.我尝试使用以下查询，但未返回确切结果. ID列为PK，其他列设置为NOT NULL.

I have table in SQL Server. I want to find last row in each group. I tried with the following query, but it does not return exact result. ID column is PK and other columns are set to NOT NULL.

select ID, Name FROM 
(select ID, Name, max(ID) over (partition by Name) as MAX_ID
from Customer) x where ID= MAX_ID

更清楚.我有2个查询.首先:

To be more clear. I have 2 queries.First:

ALTER PROCEDURE [dbo].[Ramiz_Musterija_RowNum]
@Datum DATE,
@BrojKamiona INT
AS SET NOCOUNT ON
SELECT Ime,MusterijaID,RowNum=ROW_NUMBER() OVER(ORDER BY Ime)FROM Musterije
WHERE Datum=@Datum AND BrojKamiona=@BrojKamiona  GROUP BY Ime,MusterijaID

第二个:

ALTER PROCEDURE [dbo].[Ramiz_Musterija_FindLast]
@Datum DATE,
@BrojKamiona INT
AS SET NOCOUNT ON
SELECT a.* from Musterije a
JOIN (SELECT Ime, MAX(MusterijaID) AS MAXID FROM Musterije GROUP BY Ime) AS b 
ON a.MusterijaID = b.MAXID AND a.Datum=@Datum AND a.BrojKamiona=@BrojKamiona

然后执行LINQ查询:

Then LINQ query:

  var rowNumList = from f in customerFindLastList
                     join r in customerRowNumList
                     on f.MusterijaID equals r.MusterijaID
                     select new { r.RowNum };

我试图在每一行中找到最后一行，然后匹配MusterijaID列上的这2个查询. 关于此的任何帮助将不胜感激. 这是一组的输出.现在，问题是这两个查询在"4250" MusterijaID上匹配，但是我需要在"4229"上匹配查询.

I am trying to find last row in each row,then match this 2 queries on MusterijaID column. Any help regarding this would be appreciated. This is output of one group. Now, problem is that these two queries are matched on "4250" MusterijaID, but I need to match queries on "4229".

Ime MusterijaID
100//1  4246
100//1  4247
100//1  4248
100//1  4249
100//1  4250
100//1  4229

推荐答案

select ID, Name 
FROM (select ID, Name, -- add other columns here
             ROW_NUMBER() over (partition by Name ORDER BY ID DESC) as MAX_ID
      from Customer) x
WHERE MAX_ID = 1

这篇关于按查询查找组中的最后一行-SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！