Member Avatar for buggiez

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!

  • 2 Contributors
  • 1 Reply
  • 101 Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by campkev
Member Avatar for campkev

it think this should work

select nick, max(energy.dateposted) from users
join energy
on energy.userid = user.id
group by nick
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.