Retrieving the Latest Records From a Database Table Reply

misc3_bgI have been doing SQL development for at least 15 years now but with the advent of Object Relational Mapping (ORM) frameworks like ADO.NET Entity Framework, I have had to write fewer complicated queries in recent years.

I have an application which is capturing data every 5 minutes and storing it in a table.  The challenge was that for records that shared the same Access Code and Location Id, to retrieve the record with the greatest value in the Timestamp column.  See figure 1 for the table definition and figure 2 for some sample data.

ID Data Type
ID int
Timestamp datetime
AccessCode varchar(64)
LocationId int
BandwidthAllocated float
DownloadBandwidth float
UploadBandwidth float
Figure 1:  Table Definition
Column Name Timestamp Access Code Location Id Bandwidth Allocated (Mb) Download Bandwidth (Mb) Upload Bandwidth (Mb)
1 2012-08-17 10:54:18.433 abcdef 555 5 0.75 0.25
195 2012-08-17 10:59:04.930 abcdef 555 5 0.60 0.15
389 2012-08-17 11:04:06.920 abcdef 555 5 1.05 0.25
583 2012-08-17 11:09:09.713 abcdef 555 5 2.35 1.20
777 2012-08-17 11:14:02.340 abcdef 555 5 1.15 0.60
971 2012-08-17 11:19:00.140 abcdef 555 5 0.55 0.15
Figure 2:  Table Data

For a single record, this query is pretty straight forward. Just order the records by the Timestamp field in descending order and get the Top 1 record.

See the following t-sql code example:

SELECT    TOP 1 *
FROM      AccessCodeUsage
WHERE     AccessCode = 'abcdef'
AND       LocationId = 12345
ORDER BY  Timestamp DESC

T-SQL Single Record Example

However, to get the latest record for every Access Code / Location Id combination, it becomes a little more complicated. A quick search on the Internet yielded 2 solutions. The first solution uses a nested statement with the NOT EXISTS syntax to find a record that does not have any other record with a greater Timestamp.  The second approach also uses a nested statement with the GROUP BY syntax by to get the MAX Timestamp for the Access Code / Location combinations and then retrieves the record for that Timestamp value.

See the following t-sql code example:

SELECT  * 
FROM    AccessCodeUsage a   
WHERE   NOT EXISTS (SELECT  1  
                    FROM    AccessCodeUsage  
                    WHERE   LocationId = a.LocationId 
                    AND     Timestamp > a.Timestamp) 

T-SQL Multiple Record Example 1

SELECT  * 
FROM    AccessCodeUsage a 
WHERE   a.Timestamp =  
        (SELECT    MAX(Timestamp) 
         FROM      AccessCodeUsage 
         WHERE     a.LocationId = LocationId 
         AND       a.AccessCode = AccessCode 
         GROUP BY  LocationId, AccessCode) 

T-SQL Multiple Record Example 2

Now that I have a couple of examples how to create my query in SQL, how to create the same query using ADO.NET Entity Framework object model?  And specifically, using C# Lambda functions.  The solution that I found is basically the same approach that is used in the first T-SQL example.

var last = AccessCodeUsages.Where(u1 => !mrm.AccessCodeUsages
                                            .Any(u2 => u2.LocationId == u1.LocationId &&
                                                       u2.AccessCode == u1.AccessCode &&
                                                       u2.Timestamp > u1.Timestamp));

C# Lambda Function Example

I know that this example is not rocket science but it was something that had me stumped for a little while and hopefully it might help someone else out in the future.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s