MS SQL

retrieve user's nick and latest title according to dateposted

Please support our MS SQL advertiser: Intel Parallel Studio Home

•

Join Date: Apr 2007

Posts: 1

Reputation: buggiez is an unknown quantity at this point

Solved Threads: 0

buggiez

Offline

Newbie Poster

retrieve user's nick and latest title according to dateposted

Apr 26th, 2007

Hi all

i have 2 DBs: Energy & Users

Users:
-id (pk)
-nick

Energy:
-id (pk)
-title
-userid (fk)
-dateposted

Description of workflow:
there are a number of titles in "Energy", and each user has more than 1 titles.

I would like to display Users's Nick, and LATEST TITLE (which is according to the dateposted).

How should i do it?

i tried:

select nick, energy.dateposted from users
join energy
on energy.userid = user.id

but the retrieved results = a list of records with the same nicks for few of the records.

something like that..
2007-04-25 11:40:07.000 val
2007-04-25 11:51:49.000 val
2007-04-25 14:36:45.000 val
2007-04-25 16:14:36.000 val
2007-04-25 16:23:42.000 susu
2007-04-25 16:24:20.000 susu
2007-04-25 16:29:24.000 susu

what i want is to have 2 records for this, which is something like..
2007-04-25 16:14:36.000 val
2007-04-25 16:29:24.000 susu

can anyone please help me with this? thanks alot!

•

Join Date: Jul 2005

Posts: 483

Reputation: campkev is an unknown quantity at this point

Solved Threads: 19

campkev

Offline

Posting Pro in Training

Re: retrieve user's nick and latest title according to dateposted

May 3rd, 2007

it think this should work

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
SELECT nick, max(energy.dateposted) FROM users
JOIN energy
ON energy.userid = user.id
GROUP BY nick
select nick, max(energy.dateposted) from users
join energy
on energy.userid = user.id
group by nick