Select Newest Record Group by Username in SQL Server 2008

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 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

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

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

SQL Select latest records

A simple LEFT JOIN to check that no newer status exists on an order should do it just fine;

SELECT o.* 
FROM orders o
JOIN orderStatus os
  ON o.orderID = os.orderID
LEFT JOIN orderStatus os2
  ON o.orderID = os2.orderID 
 AND os.dateTime < os2.dateTime
WHERE os.orderStatusCode = 'PENDING' AND os2.dateTime IS NULL;

SQL Finding most recent record by group

SELECT *
FROM
(
SELECT a.ID,a.Status,a.ApproverID,A.DateTime, 
       RN = ROW_NUMBER() OVER (PARTITION BY a.ID ORDER BY A.DateTime DESC)
FROM   Approval a
WHERE  NOT EXISTS
       (
           SELECT *
           FROM   Approval x
           WHERE  x.ID = a.ID
           AND    x.Status in (90, 99)
       )
) A
WHERE A.RN = 1

RN = 1 will give you the latest record by ID

NOT EXISTS() is to exclude an ID that is having 90 or 99 status

Select newest records that have distinct Name column

Select ID,Name, Price,Date
From  temp t1
where date = (select max(date) from temp where t1.name =temp.name)
order by date desc

Here is a SQL Fiddle with a demo of the above

---

Or as Conrad points out you can use an INNER JOIN (another SQL Fiddle with a demo) :

SELECT t1.ID, t1.Name, t1.Price, t1.Date 
FROM   temp t1 
INNER JOIN 
(
    SELECT Max(date) date, name
    FROM   temp 
    GROUP BY name 
) AS t2 
    ON t1.name = t2.name
    AND t1.date = t2.date 
ORDER BY date DESC 

SQL Server 2008 : selecting one record, group by

Your logic is rather complicated. The following query takes the approach of first getting all candidate "spouse" and "child" records. It then chooses one of them using row_number():

select a.*
from (select a.*,
             row_number() over (partition by group_id, type order by mem_id) as seqnum
      from tbla a
      where a.type <> 'self' and
            a.address1 <> (select address1
                           from tbla a2
                           where a2.group_id = a.group_id and
                                 a2.type = 'self'
                          )
     ) a
where seqnum = 1;

You can see it work at this SQL Fiddle.

---

Related Topics