naoliveira
New member
- Local time
- Today, 23:10
- 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.
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.