CODEWITHSUNDEEP
sqlsql-servergroup-by
coder
coder

Reputation: 716

Find last row in group by query-SQL Server

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

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

And second one: 

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

Then LINQ query:

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

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

Upvotes: 3

Views: 17736

Answers (1)

John Woo
John Woo

Reputation: 263933

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

Upvotes: 13

Related Questions