Select latest rows

Question

I have a table in a database where I want to select the latest rows from it by some conditions.

I made a fiddle to better explain my needs.

As you can see, I already tried to create a query to get the data I want to have. But unfortunately I get not only the latest rows but actually every row which fits the conditions but the date.

I try to explain what I want with pseudo sql:

SELECT * FROM test WHERE date = Max(date) AND user = 'Timmy';

EDIT: It seems like it's not totally clear what I want. 'date' describes the creation date of the profile. So I want to get every latest profile for 'Timmy'.

Answer 1

If you want to return the rows that have the most recent date associated with them, you can also use a subquery to get the result:

select t1.description,
  t1.date,
  t1.profile,
  t1.user,
  Replace(t1.locations, ',', '|') locations
from test t1
inner join
(
  select max(date) date, profile, user
  from test
  group by profile, user
) t2
  on t1.user = t2.user
  and t1.date = t2.date
  and t1.profile = t2.profile
where t1.user = 'Timmy'
order by t1.profile

See SQL Fiddle with Demo.

The subquery will return the max date for each user and profile, then it is joined back to your table on the user, profile and date to return the most recent row for each profile of each user (or, like here, for each profile of a specific user).

Answer 2

SELECT description,
       date,
       profile,
       user,
       Replace(locations, ',', '|') AS locations
FROM   test AS t
WHERE date = (
                  SELECT date
                  FROM test AS tm
                  WHERE tm.user = t.user
                  ORDER BY date DESC
                  LIMIT 1
                 )
  AND user = 'Timmy'
ORDER  BY profile ;     -- whatever