Out of 5 Records Get One With Latest Date

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

Out of 5 Records Get One With Latest Date

Author

Topic

jjmusicpro
Yak Posting Veteran

79 Posts

Posted - 2012-01-05 : 16:51:34

I have a table with multiple records for a user, 1 of the columns is a date.

I wanted to display a report that only brough back the latest entry.

Table
username
userid
userdate

Data
testguy,12321,10/11/2012 5:13
testguy,12323241,10/1/2012 5:11
testguy,1234421,10/15/2012 5:10
testguy1,1234421,10/15/2012 5:10

I would like for the response to be
testguy,1234421,10/15/2012 5:10
testguy1,1234421,10/15/2012 5:10

I just cant figure this one out, thanks guys!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-05 : 17:22:21

[code]select * from
(
select *,
row_number() over (partition by username order by userdate desc) as RN
from
YourTable
) s
where RN = 1;[/code]

jjmusicpro
Yak Posting Veteran

79 Posts

Posted - 2012-01-05 : 18:00:35

Here is the actual query i am doing now.

select l.logcomputerid,lc.computername,l.loguserid,u.username,l.logtime
form log l
inner join log computer lc on l.logcomputerid = lc.logcomputerid
inner join users u on l.loguserid = u.userid

*NOTE
This query gets me the unique correct computer data, but now i dont know how i add all the joins to it
select * from
(
select *,
row_number() over (partition by username order by userdate desc) as RN
from
YourTable
) s
where RN = 1;

*NOTE
Here is what i was able to figure out, but cant seem to get it work, it says ambiguous column nam e'logcomputerid'
select * from
(
select l.logcomputerid,lc.computername,l.loguserid,u.username,l.logtime,
row_number() over (partition by logcomputerid order by logtime desc) as RN
form log l
inner join log computer lc on l.logcomputerid = lc.logcomputerid
inner join users u on l.loguserid = u.userid
) s
where RN = 1;

The problem is, it brings back all records for each record in the log table, i wanted it to bring back the lastest date for each computer.

So here is what is returned now.

The query should just bring back the high lighted columns

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-05 : 20:09:28

Can you post the exact query you are using? The code you posted does not parse. May be this? (I am guessing)

SELECT 
   *
FROM   
(
   SELECT l.logcomputerid,
         l.computername,
         l.loguserid,
         u.username,
         l.logtime,
         ROW_NUMBER() OVER(PARTITION BY l.logcomputerid ORDER BY logtime DESC) AS RN
   FROM   [log] l
         INNER JOIN users u
            ON  l.loguserid = u.userid
) s
WHERE
   RN = 1;

jjmusicpro
Yak Posting Veteran

79 Posts

Posted - 2012-01-06 : 10:51:23

How do i get it so it wont show the column 'RN' in my kick out?

jjmusicpro
Yak Posting Veteran

79 Posts

Posted - 2012-01-06 : 12:17:20

hummm everytime i try to get the RN not to display on the kick out i get errors?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-01-06 : 12:20:43

In the SELECT list do not include the RN column. Unless there is some other error happening. BTW, what's a kick out?

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-06 : 12:32:59

>> i wanted it to bring back the lastest date for each computer.

SELECT * FROM log o WHERE EXISTS
(SELECT * FROM log i
WHERE i.ComputerName = o.ComputerName
GROUP BY ComputerName
HAVING o.logtime = MAX(i.logtime))

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources