How can I get the max() of this entry with coressponding name? 1

[tomsspc]

Hi all,

I have this table
sales ( product_id Serial Not Null,
price Numeric Not Null,
product_code Char(3) Not Null);

sales (product_id,price, product);

I want to write a query that return the products that sold with the highest volume, for example:

product_id _|_ Volume
1 | 1400

I did this:

Select Max(temp.volume)
From (Select S.product_id, Count (*) As volume
From Sales S
Group By S.product_id) As temp;

But this only gives me the highest volume without the product_id.

The query :

Select S.product_id, Count (*) As volume
From Sales S
Group By S.product_id;

gives me the product_id, and the total volume of each product shorted by product_id but not SINGLE ENTRY of a product and its volume. It gives me this:


product_id _|_ Volume
1 | 890
2 | 1400
3 | 1100


How do i get both the product_id and its volume for one with hightest volume sold?

Please Help!
Whate.

 

[nigelrivett]

Depends a bit on what's available in the language

select top 1 Product_id, volume
from
(
select S.product_id, Count (*) As volume
From Sales S
Group By S.product_id
) As temp
order by volume desc






======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[tomsspc]

Thanks nigelrivett.

///
select top 1 Product_id, volume

///

does not work. I use Postgre Sql (7.xx). Do you know something similar to "top 1" for postgre sql?

Thanks.

 

[PHV]

And what about something like this ?
Select S.product_id, Count(*) As MaxVolume
From Sales S
Group By S.product_id
Having Count(*) = (Select Max(temp.volume)
From (Select T.product_id, Count (*) As volume
From Sales T
Group By T.product_id) As temp)

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[tomsspc]

Perfect, PHV.
Thank you guys!