max(a) group by b: list all entries, not just one per b

[quaeldich]

Hi all,

I have been around sql quite some time now, but still I need help with little things.

Consider the following table [tt]sales[/tt]
[tt]
+-------------+--------+
| customer_id | amount |
+-------------+--------+
| 1 | 1000 |
| 2 | 10 |
| 2 | 2000 |
| 1 | 20 |
+-------------+--------+
[/tt]
I want to select all rows ordered by the maximum amount per customer_id:
[tt]
+-------------+--------+
| customer_id | amount |
+-------------+--------+
| 1 | 1000 |
| 1 | 20 |
| 2 | 2000 |
| 2 | 10 |
+-------------+--------+
[/tt]
The only idea I had was
[tt]select customer_id, max(amount) from sales group by customer_id;[/tt]

giving of course

[tt]
+-------------+------+
| customer_id | max |
+-------------+------+
| 1 | 1000 |
| 2 | 2000 |
+-------------+------+
[/tt]
due to the group.

How can I proceed?
Thank you in Advance, Jan

 

[QatQat]

select customer_ID, amount order by amount DESC



Bye

QatQat

Life is what happens when you are making other plans.

 

[QatQat]

select customer_ID, amount from sales order by amount DESC

Sorry I forgot the table

QatQat

Life is what happens when you are making other plans.

 

[quaeldich]

Hi again,
thanks for your reply.
I think i didn't make myself clear enough.

Your hint
[tt]select customer_ID, amount from sales order by amount DESC[/tt]
gives
[tt]
+-------------+--------+
| customer_id | amount |
+-------------+--------+
| 2 | 2000 |
| 1 | 1000 |
| 1 | 20 |
| 2 | 10 |
+-------------+--------+
[/tt]
but what I would like to have is
1. In the first row the customer_id and the largest amount
2. then all other amounts of the same customer_id
3. then the customer_id with the 2nd largest amount
4. then its other amounts

 

[r937]

what version of mysql are you one

because this puppy will require either 4.1 or a temp table

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[quaeldich]

Hi r937,
thanks for your reply.
my provider runs

mysql Ver 12.22 Distrib 4.0.20, for pc-linux (i686)

Would you tell me the temp table solution?
Thanks a lot, Jan

 

[r937]

create temporary table max_amts
 ( customer_id, max_amt )
select customer_id
     , max(amount) as max_amt
  from sales
group by customer_id
;
select m.customer_id
     , s.amount
  from max_amts as m
inner
  join sales as s
    on m.customer_id
     = s.customer_id
order 
    by m.max_amt  desc
     , m.customer_id
     , s.amount   desc
;

caution: untested


rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)