0

I have a table called user_revenue which looks like thisenter image description here

So the query I need, if ran for the user 'AAA' should only return the last row.

SELECT *
FROM user_revenue 
WHERE user_Id = 'AAA'  /*Change according to user_id*/
AND revenues > (SELECT revenues FROM (
SELECT revenues, MIN(date) FROM user_revenue
WHERE user_id='AAA' ))/*Change according to user_id*/

I managed to write this which gets the job done, but ideally I'd like to only have to type the user_id one time in the query (whereas here it needs to be typed twice), is there a way to do that?

forpas
  • 160,666
  • 10
  • 38
  • 76
lfc
  • 185
  • 6
  • Your query doesn't really make sense. You have a `MIN()` with another column but not aggregation. What is the logic for getting the "last" row? – Gordon Linoff Feb 04 '20 at 19:34

2 Answers2

1

You can alias the table user_revenue like u in the main body of the query and use u.user_Id in the subquery wherever needed:

SELECT u.*
FROM user_revenue u
WHERE u.user_Id = 'AAA'
AND u.revenues > (
  SELECT revenues FROM user_revenue
  WHERE user_Id = u.user_Id 
  AND date = (SELECT MIN(date) FROM user_revenue WHERE user_Id = u.user_Id)
)

or:

SELECT u.*
FROM user_revenue u
WHERE u.user_Id = 'AAA'
AND u.revenues > (
  SELECT revenues 
  FROM user_revenue
  WHERE user_Id = u.user_Id
  ORDER BY date
  LIMIT 1
)
forpas
  • 160,666
  • 10
  • 38
  • 76
0

If you want the most recent row for a user, you don't need any subqueries:

SELECT ur.*
FROM user_revenue ur
WHERE ur.user_Id = 'AAA'
ORDER BY date DESC
LIMIT 1;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786