CODEWITHSUNDEEP
mysqljoin
Nullx8
Nullx8

Reputation: 137

inner join select on non-key column with where clause

i have the following query which provides me a list of entries always using the lastest entry as reference. (MAX(id)) .. however max(id) is not always the neewest entry ..

so is it somehow possible to get the id of the entry with the newest date (table has a unixtime date field)

current query

SELECT tr.id,user.ispro as ispro, user.id as user_id,tr.balance_e, user.paypal FROM tr
RIGHT join user on tr.user_id=user.id
INNER JOIN (SELECT MAX(id) as maxid FROM tr where tr.status='ok'
GROUP BY tr.user_id order by tr.date desc) la 
ON la.maxid = tr.id
WHERE tr.status='ok' and tr.balance_e >= ".$mincredit." 
ORDER BY tr.balance_e desc;

every time a entry is beeing updated (date) the output is incorrect since the INNER JOIN uses MAX(id) ... any idea how to change that to always have the id of the entry where the date field is the newest one ?

Upvotes: 0

Views: 192

Answers (1)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79929

Try this instead:

SELECT 
  tr.id, 
  user.ispro as ispro, 
  user.id as user_id,
  tr.balance_e,
  user.paypal 
FROM tr
INNER JOIN 
(
   SELECT user_id, MAX(date) as maxdate
   FROM tr 
   where tr.status='ok'
   GROUP BY tr.user_id
) la ON tr.user_id = la.user_id AND tr.date = la.maxdate
RIGHT join user on tr.user_id=user.id 
WHERE tr.status     = 'ok'
  and tr.balance_e >= ".$mincredit." 
ORDER BY tr.balance_e desc;

Upvotes: 1

Related Questions