too long

Question

I have two tables, and I want to get the last enterd date.

The first table is seeker:

seeker_nic-----username
111-------------ali
222-------------umer
333-------------raza

The second one is requestblood:

id-------seeker_nic-----requireddate
1-------  111 ----------2012/10/9
2 ------- 222-----------2012/5/8
3 ------  111-----------2012/12/12
4 ------- 111-----------2012/11/12
5---------111-----------2012/09/09
6 ------- 222-----------2012/7/9
7 ------- 333 ----------2012/4/4

Now, I want to list the users one time with their last inserted date like..

s.no---- username----- requireddate
 1------- ali---------- 2012/09/09
 2------- umer--------- 2012/7/9
 3------- raza--------- 2012/4/4

I am using this query, but it shows maximum date not the latest one.

SELECT seeker.username, MAX(bloodrequest.requireddate) AS requireddate, COUNT(bloodrequest.requireddate) AS total
FROM seeker
JOIN bloodrequest
  ON seeker.seeker_nic = bloodrequest.seeker_nic
GROUP BY seeker.username

This shows the maximum date, and it shows total dates. For example, 111 has total "4", but I don't know how to show the last inserted date... I am new in PHP, please help me. :(

Thanks in advance!

Answer 1

Very simple but you will need to use subselect - acquire row with last id (this will work only if you have correct indexing - i suppose you have).

Example:

SELECT seeker_nic, username, 
(SELECT `requiredate` FROM `requestblood` WHERE `seeker_nic`=`seeker`.`seeker_nic` ORDER BY `id` DESC LIMIT 1) as `last_requiredate`
FROM `seeker`