retrieve user's nick and latest title according to dateposted

Expand Post »

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!

Similar Threads

Problems in Dynaform (Form Mail) URGENT HELP in PHP
Login and retrieve user data from database in ASP.NET
MySQL Nested Select Issue in MySQL
Is SEO a part of your business plan? in Search Engine Optimization
ASP.NET how can i display the logod user Status in ASP.NET

buggiez

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

1 posts
since Apr 2007

Permalink

May 3rd, 2007

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

it think this should work

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

campkev

Reputation Points: 14

Solved Threads: 19

Posting Pro in Training

Offline

484 posts
since Jul 2005

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MS SQL Forum Timeline: text message via sql server 2005 to mobile phone

Next Thread in MS SQL Forum Timeline: database hosting......

DaniWeb Message

Cancel Changes

User Name
Password