group by and sort problem (1 Viewer)

naoliveira

New member
Joined
Dec 14, 2005
Messages
6
Hi guys,

i have a table where I can get the record with most recent date (not the most recent record) for each user on that table, so first I made a query to sort by date then I made a query based on the date sorted query to group by user and pick the last value, but the last value is always from the original table not from the query, I made this:

utentes = users

q_date_sorted (q_utentes_mensalidades_ordena_data)
SELECT utentes_mensalidades.*
FROM utentes_mensalidades
ORDER BY utentes_mensalidades.data;

Now I wanna pick the last value of Valor field for each user (utente)
q_pick_last_value
SELECT utente_id, Last(valor) AS LastOfvalor
FROM q_utentes_mensalidades_ordena_data
GROUP BY utente_id;

If in the original table is, for user 1:
date ---- value
1-1-6 ---- 100
1-3-6 ---- 200
1-2-6 ---- 300

(dates are in american format)
the result should be 200 (most recent date)
but I am getting 300 (most recent record)

I hope it's clear,

regards,
Nuno.
 

Jon K

Registered User
Joined
May 22, 2002
Messages
2,209
You can build a Totals Query to select the Max date (not the Last date) for each user id and then, in a second query, join the table with the Totals Query to return the records from the table.


q_one:-
SELECT utente_id, Max(data) AS MaxOfdata
FROM utentes_mensalidades
GROUP BY utente_id;

q_two:-
SELECT utentes_mensalidades.utente_id, utentes_mensalidades.data, utentes_mensalidades.valor
FROM utentes_mensalidades INNER JOIN q_one ON (utentes_mensalidades.utente_id = q_one.utente_id) AND (utentes_mensalidades.data = q_one.MaxOfdata);


Run the second query.
.
 

naoliveira

New member
Joined
Dec 14, 2005
Messages
6
Thanks a lot Jon, that's exactly what I need:) :) :) :)
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom