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
How to Convert Spark Schemardd into Rdd of My Case Class
How to Correctly Handle Dates in Queries Constraints
Update Xml Tag in a Clob Column in Oracle
Postgresql Return a Function with a Custom Data Type
General Rules for Simplifying SQL Statements
Save Output from SQL Function to CSV File (Copy) with Dynamic Filename
Copy from One Database to Another Using Oracle SQL Developer - Connection Failed
Sp Taking 15 Minutes, But the Same Query When Executed Returns Results in 1-2 Minutes
Why Would Year Fail with a Conversion Error from a Date
Select Top Distinct Results Ordered by Frequency
How to Add Multiple "Not Like '%%' in the Where Clause of SQLite3
How to Write This SQL Query in Mongodb Syntax
Dataset Panel (Report Data) in Ssrs Designer Is Gone
Select Count of Rows in Another Table in a Postgres Select Statement