Re: SQL puzzle

To find if more than one is different than cero at different dates:

select client, transaction, min(date), max(date) from file
where number <> 0
group by client, transaction
having min(date) < max(date)

Also, if you only need to know if there is more than one:

select client, transaction, count(*) from file
where number <> 0
group by client, transaction
having count(*) > 1


David FOXWELL wrote:

Can anyone help?


Here is My Transaction File:

Client Transaction date number
X Y 20090101 55
X Y 20090201 0

The field <number> is usually 0, but if not, the user <thinks> that this will at the first transaction for a client, ie, minimum date value.
I want to prove or disprove this by extracting the 3rd line in the example, if it exists :

Client Transaction date number
X Y 20090101 55
X Y 20090201 0
X Y 20090301 2

I want any line where number<>0 and date<> min date for client, transaction.

How can I do this?
I tried something like :

With t as ( select client, transaction, min(date) from file group by etc)

Then joining to the file where number >0 and date > min(date), but my results aren't right.



Thanks.