Hi,

I have a table structured as follow:
id | user_id | credit
---+---------+-----------
1 | 1 | 5
2 | 1 | -5
3 | 2 | 10
4 | 1 | -7
5 | 2 | 6
6 | 2 | -3
7 | 3 | 9

I need to be able to get the user_id where the last amount entered is
negative (<0)
In that example, I would want to get the user_id 1 and 2

id is auto-incremental

I cannot use sub-select as mysql version is < 4.1 and I would prefer to
have it all in one query as there is more processing after...

Help, it is driving me crazy

Thanks for the help

Alex.HANIN [at] ntlworld.com wrote:
> Hi,
>
> I have a table structured as follow:
> id | user_id | credit
> ---+---------+-----------
> 1 | 1 | 5
> 2 | 1 | -5
> 3 | 2 | 10
> 4 | 1 | -7
> 5 | 2 | 6
> 6 | 2 | -3
> 7 | 3 | 9
>
> I need to be able to get the user_id where the last amount entered is
> negative (<0)
> In that example, I would want to get the user_id 1 and 2
>
> id is auto-incremental
>
> I cannot use sub-select as mysql version is < 4.1 and I would prefer to
> have it all in one query as there is more processing after...
>
> Help, it is driving me crazy
>
> Thanks for the help

easy :-)

SELECT t1. *
FROM transactions t1
LEFT JOIN transactions t2 ON t1.user_id = t2.user_id
AND t1.id < t2.id
WHERE t2.id IS NULL
AND t1.credit<0

You are a star :D
Thanks a bunch