SQL

sql select first and last record of each group

select v.*
from (select v.*,
             row_number() over (partition by vehicleid, tripid order by datetime asc) as seqnum_asc,
             row_number() over (partition by vehicleid, tripid order by datetime desc) as seqnum_desc
      from vehicles v
     ) v
where seqnum_asc = 1 or seqnum_desc = 1;

sql server select last row of each item in group by column

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

sql server select last row of each item in group by column

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 select last row of each item in group by column

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

sql server select last row of each item in group by column

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

sql server select last row of each item in group by column

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