Introduction:
Here I will explain how to write query to get latest or recent records but unique records in SQL Server.
Here I will explain how to write query to get latest or recent records but unique records in SQL Server.
Description:
In
previous articles I explained joins in SQL Server, Get Records without weekends, SQL Query Get Month Wise Data, difference between function and stored
procedures,
and
many articles relating to SQL
Server.
Now I will explain how to write a query to get latest or recent records but unique
records in SQL
Server.
If
we want to get recent status of records but only unique records we need to
write the query like as shown below
SELECT Column1,
Column2, DateColumn FROM
[TABLE1] INNER JOIN
(
SELECT MAX(DateColumn) AS LatestDate,
[Column1]
FROM [TABLE1]
GROUP BY [Column1]
) MAXTABLE ON [TABLE1].DateColumn =
MAXTABLE.LatestDate
AND [TABLE1].[Column1]
= MAXTABLE.[Column1]
|
If
you want to see it in complete example first design one table (BusinessInfo) with three columns
in database and enter some dummy data like as show below
Business
|
Status
|
CrDate
|
Travel Agents
|
New
|
2012-11-28 09:00:00.000
|
Travel Agents
|
Processed
|
2012-11-28 11:00:00.000
|
Home Loans
|
New
|
2012-11-11 10:30:00.000
|
Home Loans
|
Processed
|
2012-11-29 08:30:22.000
|
Home Loans
|
Processed
|
2012-11-29 11:30:22.000
|
pakcers & Movers
|
New
|
2012-11-30 07:25:00.000
|
pakcers & Movers
|
New
|
2012-11-30 12:25:00.000
|
Education
|
New
|
2012-11-15 10:30:00.000
|
Now
from above table I need to get recent records but unique for that we need to
write the query like as shown below
SELECT b.Business,[Status],CrDate FROM BusinessInfo b INNER
JOIN
(
SELECT MAX(CrDate) AS LatestDate,
[Business]
FROM BusinessInfo
GROUP BY
[Business]
) SUBTABLE ON b.CrDate = SUBTABLE.LatestDate
AND b.Business = SUBTABLE.Business
|
Once
we run above query we will get output like as shown below
Output
Business
|
Status
|
CrDate
|
Travel Agents
|
Processed
|
2012-11-28 11:00:00.000
|
pakcers & Movers
|
New
|
2012-11-30 12:25:00.000
|
Home Loans
|
Processed
|
2012-11-29 11:30:22.000
|
Education
|
New
|
2012-11-15 10:30:00.000
|
|
If you enjoyed this post, please support the blog below. It's FREE! Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email. |
|||
Subscribe by RSS
Subscribe by Email
|
|||
2 comments :
WITH TEMP
AS
(
SELECT ROW_NUMBER() OVER(PARTITION BY CATEGORY_ID ORDER BY PURCHASED_DATE DESC) AS RNO,CATEGORY_ID,PRODUCT_NAME,PURCHASED_DATE FROM PRODUCAT_TABLE
)
SELECT * FROM TEMP WHERE RNO=1
How to Get Latest Updated Records of table??
Note: Only a member of this blog may post a comment.