DISTINCT results based on the value of one Col? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

DISTINCT results based on the value of one Col?

Author

Topic

Jaypoc
Yak Posting Veteran

58 Posts

Posted - 2008-06-19 : 15:14:27

Here's one thats had me and a coworker puzzled. Hopefully it's something simple:

TABLE A: (log of messages)
userid, int
date, datetime
message, varchar(50)

Table A Data:
1, 6/18/2008 @ 2:32:41, This is my message
1, 6/18/2008 @ 2:31:02, This is my message
1, 6/17/2008 @ 7:34:26, This is another message
2, 6/18/2008 @ 2:32:41, This is not his message
2, 6/16/2008 @ 11:21:32, This is my message

TABLE B: (List of users)
userid, int
name, varchar(100)

Table B Data:
1, John
2, Mike

I want to extract the most recent message logged per user, i.e.:

name | date | message
--------|---------------------|-------------------------
John | 6/18/2008 @ 2:32:41 | This is my message
Mike | 6/18/2008 @ 2:32:41 | This is not his message.

I have been unable to come up with a query that can return just the one value. I've tried variants of:

SELECT DISTINCT b.name, a.date, a.message
FROM a INNER JOIN
b ON a.userid = b.userid

including sub-queries and even played with the visual diagrams trying to design this in Enterprise Manager but none of the combinations I tried work. Is there an easy way to do this via a query? I don't have experience with stored procedures. Would that be necessary?

TIA

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-06-19 : 15:35:20

see:

http://sqlblog.com/blogs/adam_machanic/archive/2008/02/08/who-s-on-first-solving-the-top-per-group-problem-part-1-technique.aspx

for some ideas.

- Jeff
http://weblogs.sqlteam.com/JeffS

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-20 : 00:37:31

[code]SELECT b.name, a.date, a.message
FROM a INNER JOIN
b ON a.userid = b.userid
INNER JOIN (SELECT userid,MAX(date) as MaxDate
FROM a
GROUP BY userid) t
ON a.userid=t.userid
AND a.date=t.MaxDate[/code]

Jaypoc
Yak Posting Veteran

58 Posts

Posted - 2008-06-20 : 11:41:50

@jsmith8858 - Thanks. That's a great reference

@visakh16 - Thank you as well. The query worked great. Does exactly what I needed (I wanted to reverse the order but that was easy)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-20 : 14:25:53

quote:
Originally posted by Jaypoc

@jsmith8858 - Thanks. That's a great reference

@visakh16 - Thank you as well. The query worked great. Does exactly what I needed (I wanted to reverse the order but that was easy)

You're welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources