Aspdotnet-Suresh

aspdotnet-suresh offers C#.net articles and tutorials,csharp dot net,asp.net articles and tutorials,VB.NET Articles,Gridview articles,code examples of asp.net 2.0 /3.5,AJAX,SQL Server Articles,examples of .net technologies

Introduction:

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 RSS subscribe by email Subscribe by Email

2 comments :

PRINCE said...


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

Irappa Pattanashetti said...

How to Get Latest Updated Records of table??

Give your Valuable Comments

Other Related Posts

© 2010-2012 Aspdotnet-Suresh.com. All Rights Reserved.
The content is copyrighted to Suresh Dasari and may not be reproduced on other websites without permission from the owner.