Hi,

I have a table which contains subscriptions from students, bu they are
tagged with a date (so I have a kind of history).
Both 'email' and 'datum' are primary keys.

How can I get a full list of every subscription, but only the last of each
student?

My SQL knowledge is too small to solve this, I'm affraid. Any help welcome.

Regards,
Toni.

Toni Van Remortel wrote:
> Hi,
>
> I have a table which contains subscriptions from students, bu they are
> tagged with a date (so I have a kind of history).
> Both 'email' and 'datum' are primary keys.
>
> How can I get a full list of every subscription, but only the last of each
> student?
>
> My SQL knowledge is too small to solve this, I'm affraid. Any help welcome.
>
> Regards,
> Toni.

assuming that datum is a date using a DATE or DATETIME or TIMESTAMP type,
then you can use the MAX function:

select email, max(datum) from subscriptions group by email;


If you are not using any of the above column types, you may not be able to
do what you want.

ciao
gmax

--
_ _ _ _
(_|| | |(_|><
_|
http://gmax.oltrelinux.com

Giuseppe Maxia wrote:

> Toni Van Remortel wrote:
>> Hi,
>>
>> I have a table which contains subscriptions from students, bu they are
>> tagged with a date (so I have a kind of history).
>> Both 'email' and 'datum' are primary keys.
>>
>> How can I get a full list of every subscription, but only the last of
>> each student?
>>
>> My SQL knowledge is too small to solve this, I'm affraid. Any help
>> welcome.
>>
>> Regards,
>> Toni.
>
> assuming that datum is a date using a DATE or DATETIME or TIMESTAMP type,
> then you can use the MAX function:
>
> select email, max(datum) from subscriptions group by email;

Wonderfull. I was seeking for JOIN and subquery's etc.
Seems it to be a simple GROUP BY command.

Thank you!

Giuseppe Maxia wrote:

> Toni Van Remortel wrote:
>> Hi,
>>
>> I have a table which contains subscriptions from students, bu they are
>> tagged with a date (so I have a kind of history).
>> Both 'email' and 'datum' are primary keys.
>>
>> How can I get a full list of every subscription, but only the last of
>> each student?
>>
>> My SQL knowledge is too small to solve this, I'm affraid. Any help
>> welcome.
>>
>> Regards,
>> Toni.
>
> assuming that datum is a date using a DATE or DATETIME or TIMESTAMP type,
> then you can use the MAX function:
>
> select email, max(datum) from subscriptions group by email;
>
>
> If you are not using any of the above column types, you may not be able to
> do what you want.

Still a problem:

SELECT email, naam, def_id, workshop_id_1, workshop_id_2, workshop_id_3,
max( datum ) as datum FROM 2006_subscriptions GROUP BY email ORDER BY datum

selects the max(datum) as it should, but not the values of workshop_id_X
that belong to the max(datum) row.

Idea?

Toni Van Remortel wrote:
[SNIP]
>
>
> Still a problem:
>
> SELECT email, naam, def_id, workshop_id_1, workshop_id_2, workshop_id_3,
> max( datum ) as datum FROM 2006_subscriptions GROUP BY email ORDER BY datum
>
> selects the max(datum) as it should, but not the values of workshop_id_X
> that belong to the max(datum) row.
>
> Idea?

Only the columns that you use in the GROUP BY clause are meaningful in
such a query.
If you need more columns, then you must use a subquery.

SELECT
email, naam, def_id,
workshop_id_1, workshop_id_2,
workshop_id_3, datum
FROM
2006_subscriptions
WHERE
(email, datum) IN (select email, max(datum) from 2006_subscriptions GROUP BY email)
ORDER BY
datum

ciao
gmax

--
_ _ _ _
(_|| | |(_|><
_|
http://gmax.oltrelinux.com

Toni Van Remortel wrote:
> SELECT email, naam, def_id, workshop_id_1, workshop_id_2, workshop_id_3,
> max( datum ) as datum FROM 2006_subscriptions GROUP BY email ORDER BY datum

There's a rule about GROUP BY that you should understand. It applies to
any database product, not just MySQL.

Every field in your select list that is not part of an aggregate
function (e.g. MAX, MIN, COUNT, SUM, AVG), _must_ be in the group by
list, or else you get an ambiguous result.

Consider the following example:

|__A__|__B__|__C__|
| a | 1 | x |
| a | 1 | y |
| a | 2 | z |
| a | 2 | w |


SELECT COUNT(A), B FROM MyTABLE GROUP BY B;

Prints:

|_COUNT_|__B__|
| 2 | 1 |
| 2 | 2 |

SELECT COUNT(A), B, C FROM MyTABLE GROUP BY B;

|_COUNT_|__B__|__C__|
| 2 | 1 | x |
| 2 | 2 | z |

What happened to 'y' and 'w'? The answer is that if you list a field
such as `C` in the select list, without also listing it in the GROUP BY
clause, then it's ambiguous to the SQL engine which row to take the
value of `C` from, when presenting the final result. When grouping
solely by `B`, there are two rows in each group, but only one row in the
result. The field returning the value of `C` can holds only one value
per row, and your query hasn't done anything to specify which row from
which to take that value. So the SQL engine decides for you, somewhat
arbitrarily.

In some RDBMS products, a query such as this that creates an ambiguous
result set actually results in an error, and won't execute the query or
return any result. You _must_ change the query to make it unambiguous.

But in MySQL, the query is permitted, and it is assumed that you know
what you are doing and you accept the ambiguous result, even though it
has lost some information from the underlying data.

Regards,
Bill K.