By RyanF


2011-01-20 19:58:15 8 Comments

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!

2 comments

@Gordon Linoff 2018-05-18 14:46:12

In SQL Server, the most performant solution is often a correlated subquery:

select t.*
from t
where t.lastupdate = (select max(t2.lastupdate)
                      from t t2
                      where t2.computername = t.computername
                     );

In particular, this can take advantage of an index on (computername, lastupdate). Conceptually, the reason this is faster than row_number() is because this query simply filters out the rows that don't match. The row_number() version needs to attach to the row number to all rows, before it filters -- that is more data processing.

@Askar Ibragimov 2018-05-21 08:10:41

Could you possibly comment, what I shall do if I need to have "inner" select to contain rows with a max value of some other column "id"? I have a case where I have non-unique t2.lastupdate and different ID column, so I need to select also by max(ID) (with performance considerations of course)

@Gordon Linoff 2018-05-21 13:18:01

@AskarIbragimov . . . You should ask a question, not a comment.

@Askar Ibragimov 2018-05-22 11:56:42

@Joe Stefanelli 2011-01-20 20:05:41

;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

@RyanF 2011-01-20 20:24:01

Joe, that worked great. I never knew about the WITH clause and never used OVER or PARTITION before. Can you briefly tell me what these are doing. I looked them up, but now sure I am finding the correct info.

@Joe Stefanelli 2011-01-20 20:34:28

@RyanF: The WITH clause defines a Common Table Expression or CTE. row_number is a window function. Both are features that were introduced in SQL Server 2005. Hopefully these links can get you started in the right direction.

Related Questions

Sponsored Content

41 Answered Questions

[SOLVED] How to return only the Date from a SQL Server DateTime datatype

33 Answered Questions

[SOLVED] How do I UPDATE from a SELECT in SQL Server?

18 Answered Questions

[SOLVED] How can I get column names from a table in SQL Server?

7 Answered Questions

[SOLVED] How to turn IDENTITY_INSERT on and off using SQL Server 2008?

  • 2011-08-15 09:37:29
  • Beginner
  • 895252 View
  • 431 Score
  • 7 Answer
  • Tags:   sql sql-server-2008

10 Answered Questions

[SOLVED] Rename column SQL Server 2008

10 Answered Questions

[SOLVED] Connect different Windows User in SQL Server Management Studio (2005 or later)

  • 2009-05-11 17:31:21
  • Matt P.
  • 260381 View
  • 321 Score
  • 10 Answer
  • Tags:   sql sql-server-2008

19 Answered Questions

10 Answered Questions

10 Answered Questions

[SOLVED] Extracting hours from a DateTime (SQL Server 2005)

8 Answered Questions

Sponsored Content