|
|||||||||
|
|||||||||
|
|||||||||
 |
|||||||||
| |
||
|
|
|
|
|||||||||
|
|||||||||
|
|||||||||
|
|||||||||
| |
||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
ASP Free Forums Sponsor:
|
|
|
|
#1
August 7th, 2004, 05:56 AM
|
|||
|
|||
|
Query for latest blog record for each user
I have a simple blog system by where users can leave an entry in a diary. I want to list all users along with their latest entry. It seems simple but I just can't do it. DISTINCT doesn't work because I want to get more than 1 field returned but only want the UserID to be distinct.
There is a users table, and a diary table eg. Users ID Name 1 Tom 2 Jane Diary ID UserID Entry 1 1 Test 2 2 Test2 3 2 Test3 4 1 Test4 So I want the returned records to be like: ID UserID Entry 3 2 Test3 4 1 Test4 Ie, Each user with one latest diary entry. Any tips would be gratefully received! Thanks, Kevin
|
|
#2
August 7th, 2004, 04:27 PM
|
||||
|
||||
|
Code:
SELECT A.Username, B.Entry FROM Users As A INNER JOIN Diary As B On (A.UserID = B.UserID) WHERE B.ID IN ( SELECT MAX(ID) FROM Diary GROUP BY UserID ) ORDER BY A.Username
|
|
| Viewing: ASP Free Forums > Database > Microsoft SQL Server > Query for latest blog record for each user |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
|
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
