find latest date for this data

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

find latest date for this data

Author

Topic

pat_smith1969
Starting Member

3 Posts

Posted - 2011-08-24 : 14:07:51

hi I have a sql question for you...

I have a table that has the below colums and data


userid     machineid     date        time         someotherdata
psmith     x1          08/23/11      15:21:00      fubar
george     x2          08/21/11      14:21:00      someotherfubar
psmith     x3          08/24/11      08:23:01      something
geroge     x2          08/24/11      15:43:08       somethingelse
suse       x1          08/22/11       07:53:11      somedata
cloe       x3          08/25/11       09:00:21      somedata

so what I want is to return the row where each machine was logged into most recently...so the results would look like.


psmith    x1      08/23/11      15:21:00             fubar
geroge    x2      08/24/11      15:43:08             somethingelse
cloe      x3      08/25/11      09:00:21             somedata

if it helps I have full control of the data and database so if I need to create views or more tables I can do that.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-08-24 : 14:16:27

I think this will work (hint, it makes it easier to test if you provide sample data in a consumable format):

SELECT
    userid,
    machineid,
    [date],
    [time],
    someotherdata,
FROM
    (
        SELECT 
            userid,
            machineid,
            [date],
            [time],
            someotherdata,
            ROW_NUMBER() OVER (PARTITION BY UserID ORDER BY [Date] DESC, [Time] DESC) AS RowNum
        FROM
            MyTable
    ) AS T
WHERE
    RowNum = 1

Kristen
Test

22859 Posts

Posted - 2011-08-24 : 14:19:42

[code]
psmith x1 08/23/11 15:21:00 fubar
[/code]
assuming that should have been the 08/24/11 record, and machineid=x3 then:
[code]
CREATE TABLE #TEMP
(
T_userid varchar(20),
T_machineid varchar(10),
T_Date datetime,
T_someotherdata varchar(20)
)

INSERT INTO #TEMP
SELECT 'psmith', 'x1', '20110823 15:21:00', 'fubar' UNION ALL
SELECT 'george', 'x2', '20110821 14:21:00', 'someotherfubar' UNION ALL
SELECT 'psmith', 'x3', '20110824 08:23:01', 'something' UNION ALL
SELECT 'geroge', 'x2', '20110824 15:43:08', 'somethingelse' UNION ALL
SELECT 'suse', 'x1', '20110822 07:53:11', 'somedata' UNION ALL
SELECT 'cloe', 'x3', '20110825 09:00:21', 'somedata'

SELECT [T_RowNumber],
*
FROM
(
SELECT [T_RowNumber] = ROW_NUMBER()
OVER
(
PARTITION BY T_userid
ORDER BY T_userid, T_Date DESC
),
X.*
FROM
(
SELECT *
FROM #TEMP
) AS X
) AS T
WHERE T_RowNumber <= 1
ORDER BY T_Date DESC
[/code]

Kristen
Test

22859 Posts

Posted - 2011-08-24 : 14:21:23

Results:



T_RowNumber T_RowNumber T_userid T_machineid T_Date                  T_someotherdata
----------- ----------- -------- ----------- ----------------------- ---------------
1           1           cloe     x3          2011-08-25 09:00:21.000 somedata
1           1           geroge   x2          2011-08-24 15:43:08.000 somethingelse
1           1           psmith   x3          2011-08-24 08:23:01.000 something
1           1           suse     x1          2011-08-22 07:53:11.000 somedata
1           1           george   x2          2011-08-21 14:21:00.000 someotherfubar

pat_smith1969
Starting Member

3 Posts

Posted - 2011-08-24 : 14:43:44

Thanks for the real quick response... I didn't make my self real clear...my fault.

I need the machine to be distinct and then tell me what the last person was to logon to that machine (or return the row actually).

so in my example it will return one row for machine x1 that was logged onto most recently, one row for x2, and one row for x3. if it could order by machineid that would be real cool too but that is icing on the cake.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-08-24 : 15:49:32

[code]SELECT
userid,
machineid,
[date],
[time],
someotherdata,
FROM
(
SELECT
userid,
machineid,
[date],
[time],
someotherdata,
ROW_NUMBER() OVER (PARTITION BY machineid ORDER BY [Date] DESC, [Time] DESC) AS RowNum
FROM
MyTable
) AS T
WHERE
RowNum = 1
ORDER BY
machineid
[/code]

pat_smith1969
Starting Member

3 Posts

Posted - 2011-08-24 : 16:24:07

that worked perfectly...
if you don't mind answering a few questions... I prefer to understand rather than just cut and paste...

I see you put a select statement inside a select statement... basically you are reordering the table then doing you select from that... ???

also I have not seen the OVER command before, nor the partition command....or the row_number before...I will google each of them but if you could go over your logic (something you cannot google) I would really apprieciate it. thanks.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-08-24 : 16:33:09

If you have BOL (Books Online) or you can get the MS site that has it you can look up the ROW_NUMBER function. Alternatively, you can search for: Ranking Functions (Transact-SQL)

If you want, you can see what the inner query is doing by executing that by itself. The ROW_NUMBER function is basically assigning numbers to rows. In this case we PARTITION BY the machineid since that is what we want create a sequence per group (kind of like doing a GROUP BY on the machine id). The ORDER BY in the OVER clause tells SQL how to assign the row numbers for the sequence group. So, in English, we are asking SQL to create sequence groups on the machine id and order the sequence by date and time (desc).

The outer select just gets the rows from the inner select where the RoNum is equal to 1. Since we ordered by Date and Time Descending, rownum = 1 will be the last row (by date/time) for that sequence group (machine id).

Hopefully, that make sense. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources