How to Get the First and the Last Record Per Group in SQL Server 2008

How to get the first and the last record per group in SQL Server 2008?

How about using ROW_NUMBER:

SQL Fiddle

WITH Cte AS(
    SELECT *,
        RnAsc = ROW_NUMBER() OVER(PARTITION BY [group] ORDER BY val),
        RnDesc = ROW_NUMBER() OVER(PARTITION BY [group] ORDER BY val DESC)
    FROM tbl
)
SELECT
    id, [group], val, start, [end]
FROM Cte
WHERE
    RnAsc = 1 OR RnDesc = 1
ORDER BY [group], val

How to get the last record per group in SQL

You can use a ranking function and a common table expression.

WITH e AS
(
     SELECT *,
         ROW_NUMBER() OVER
         (
             PARTITION BY ApplicationId
             ORDER BY CONVERT(datetime, [Date], 101) DESC, [Time] DESC
         ) AS Recency
     FROM [Event]
)
SELECT *
FROM e
WHERE Recency = 1

Retrieving last record in each group from database - SQL Server 2005/2008

;with cteRowNumber as (
    select COMPUTERNAME, SERIALNUMBER, USERNAME, LASTIP, LASTUPDATE, SOURCE,
           row_number() over(partition by COMPUTERNAME order by LASTUPDATE desc) as RowNum
        from YourTable
)
select COMPUTERNAME, SERIALNUMBER, USERNAME, LASTIP, LASTUPDATE, SOURCE
    from cteRowNumber
    where RowNum = 1

How to get first record out of each group from the result retrieved by using group by command

declare @T table (ID int, GroupID int, Qty int)
insert into @T values
(1, 1, 100),
(2, 1, 200),
(3, 1, 300),
(4, 2, 98),
(5, 2, 198),
(6, 3, 175),
(7, 3, 275),
(8, 3, 375),
(9, 4, 215)

;with cte as
(
  select
    ID,
    GroupID,
    Qty,
    rank() over(partition by GroupID order by ID) as rn
  from @T
)  
select ID, GroupID, Qty
from cte
where rn = 1

Get the latest records per Group By SQL

The rank window clause allows you to, well, rank rows according to some partitioning, and then you could just select the top ones:

SELECT oDate, oName, oItem, oQty, oRemarks
FROM   (SELECT oDate, oName, oItem, oQty, oRemarks,
               RANK() OVER (PARTITION BY oName ORDER BY oDate DESC) AS rk
        FROM   my_table) t
WHERE  rk = 1

Get top 1 row of each group

;WITH cte AS
(
   SELECT *,
         ROW_NUMBER() OVER (PARTITION BY DocumentID ORDER BY DateCreated DESC) AS rn
   FROM DocumentStatusLogs
)
SELECT *
FROM cte
WHERE rn = 1

If you expect 2 entries per day, then this will arbitrarily pick one. To get both entries for a day, use DENSE_RANK instead

As for normalised or not, it depends if you want to:

• maintain status in 2 places
• preserve status history
• ...

As it stands, you preserve status history. If you want latest status in the parent table too (which is denormalisation) you'd need a trigger to maintain "status" in the parent. or drop this status history table.

Selecting First and Last record from a table in SQL Server

Use MAX and MIN:

SELECT 
  MAX(titlenumber) AS MaxTitleNumber, 
  MIN(titlenumber) AS MinTitleNumber
FROM cataloguebase

Get the first and last record of each item for the month

You are getting the same quantities because you are aggregating by quantity in the group by as well as product. Your version of the query, properly written would be:

SELECT ProductID, MIN(Quantity) AS Starting, MAX(Quantity) AS Ending 
FROM Records
WHERE DateAdded BETWEEN '2014-04-01' AND '2014-04-30'
GROUP BY ProductID;

However, this doesn't give you the first and last values. It only gives you the minimum and maximum ones. To get those values, use row_number() and conditional aggregation:

SELECT ProductID,
       MAX(CASE WHEN seqnum_asc = 1 THEN Quantity END) as Starting,
       MAX(CASE WHEN seqnum_desc = 1 THEN Quantity END) as Ending
FROM (SELECT r.*,
             row_number() over (partition by product order by dateadded asc) as seqnum_asc,
             row_number() over (partition by product order by dateadded desc) as seqnum_desc
      FROM Records r
     ) r
WHERE DateAdded BETWEEN '2014-04-01' AND '2014-04-30'
GROUP BY ProductID;

If you are using SQL Server 2012, then you can also use this with FIRST_VALUE() and LAST_VALUE() instead of row_number().

Select last records from table using group by

Assuming that the start and end dates will always be the highest values then you need to drop some of the columns from the GROUP BY (having all the columns in the GROUP BY is kinda like using DISTINCT) and use an aggregate function on the other column:

SELECT UserId,
       MAX(StartDate) AS StartDate,
       MAX(EndDate) AS EndDate
FROM usersworktime
GROUP BY UserId;

Otherwise, if that isn't the case, you can use a CTE and ROW_NUMBER:

WITH CTE AS(
    SELECT UserID,
           StartDate,
           EndDate,
           ROW_NUMBER() OVER (PARTITION BY UserID ORDER BY UsersWordTimeID DESC) AS RN
    FROM usersworktime)
SELECT UserID,
       StartDate,
       EndDate
FROM CTE
WHERE RN = 1;

---

Related Topics