Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Retrieving last record in each group from database - SQL Server 2005/2008

I have done some seaching by can't seem to get the results I am looking for. Basically we have four different management systems in place throughout our company and I am in the process of combining all the data from each system on a regular basis. My goal is to update the data every hour into a central database. Here is a sample data set I am working with:

COMPUTERNAME | SERIALNUMBER | USERNAME | LASTIP | LASTUPDATE | SOURCE TEST1 | 1111 | BOB | 1.1.1.1 | 1/17/2011 01:00:00 | MGMT_SYSTEM_1 TEST1 | 1111 | BOB | 1.1.1.1 | 1/18/2011 01:00:00 | MGMT_SYSTEM_2 TEST1 | 1111 | PETER | 1.1.1.11 | 1/19/2011 01:00:00 | MGMT_SYSTEM_3 TEST2 | 2222 | GEORGE | 1.1.1.2 | 1/17/2011 01:00:00 | MGMT_SYSTEM_1 TEST3 | 3333 | TOM | 1.1.1.3 | 1/19/2011 01:00:00 | MGMT_SYSTEM_2 TEST4 | 4444 | MIKE   | 1.1.1.4 | 1/17/2011 01:00:00 | MGMT_SYSTEM_1 TEST4 | 4444 | MIKE   | 1.1.1.41 | 1/19/2011 01:00:00 | MGMT_SYSTEM_3 TEST5 | 5555 | SUSIE  | 1.1.1.5 | 1/19/2011 01:00:00 | MGMT_SYSTEM_1 

So I want to query this master table and only retrieve the latest record (based on LASTUPDATE) that way I can get the latest info about that system. The problem is that one system may be in each database, but of course they will never have the same exact update time.

I would expect to get something like this:

TEST1 | 1111 | PETER | 1.1.1.11 | 1/19/2011 01:00:00 | MGMT_SYSTEM_3 TEST2 | 2222 | GEORGE | 1.1.1.2 | 1/17/2011 01:00:00 | MGMT_SYSTEM_1 TEST3 | 3333 | TOM | 1.1.1.3 | 1/19/2011 01:00:00 | MGMT_SYSTEM_2 TEST4 | 4444 | MIKE   | 1.1.1.41 | 1/19/2011 01:00:00 | MGMT_SYSTEM_3 TEST5 | 5555 | SUSIE  | 1.1.1.5 | 1/19/2011 01:00:00 | MGMT_SYSTEM_1 

I have tried using the MAX function, but with that I can only retrieve one column. And I can't use that in a subquery because I don't have a unique ID field that would give me the last updated record. One of the systems is a MySQL database and the MAX function in MySQL will actually work the way I need it to only returning one record per GROUP BY, but it doesn't work in SQL Server.

I'm thinking I need to use MAX and a LEFT JOIN, but my attempts so far have failed.

Your help would be greatly appreciated. I have been racking my brain for the past 3-4 hours trying to get a working query. This master table is located on a SQL Server 2005 server.

Thanks!

like image 775
RyanF Avatar asked Jan 20 '11 19:01

RyanF


People also ask

How do I get the latest record for each ID in SQL Server?

Here is the syntax that we can use to get the latest date records in SQL Server. Select column_name, .. From table_name Order By date_column Desc; Now, let's use the given syntax to select the last 10 records from our sample table.

How do I select the last record in SQL?

To get the last record, the following is the query. mysql> select *from getLastRecord ORDER BY id DESC LIMIT 1; The following is the output. The above output shows that we have fetched the last record, with Id 4 and Name Carol.

How do I find the last row in SQL Server?

If the table is indexed on the sort column, then SQL will just read the last row of the table. No expensive sort or full table scan is needed. @Sri You would execute SELECT TOP 1000 * FROM table_name ORDER BY column_name DESC and should output the last 1000 records.


1 Answers

;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 
like image 164
Joe Stefanelli Avatar answered Sep 17 '22 17:09

Joe Stefanelli