* Roland Niederbrucker
> from the following Product-table
>
> ProductId
>
> MarketId
>
> CustomerId
>
> Type
>
> Price
>
> ...
>
> i would like to get the three highest and lowest prices
>
> for each group of ProductId,MarketId & Type.
>
> How could i do that ???
Do you use a programming language, or are you looking for a pure SQL
solution? What version of mysql are you using? It is easy to do this
programatically, the new mysql 5.0 with stored procedures could have helped
you, the GROUP_CONCAT() function from 4.1 could be used, and unions from 4.0
maybe could be used too.
To do it programatically, any mysql version (python-ish metacode):
rs = q("SELECT ProductId,MarketId,Type,Price FROM Products
ORDER BY ProductId,MarketId,Type,Price DESC")
p = m = t = false
counter = 0
for row in rs:
P,M,T = row["ProductId"],row["MarketId"],row["Type"]
if ((P == p) and (M == m) and (T == t)):
if counter < 3:
print row["Price"],
counter = counter + 1
else:
print "\n"+P,M,T,row["Price"],
counter = 1
p,m,t = P,M,T
That should output the three highest prices for each group, repeat without
the "DESC" in the SELECT to get the three lowest prices.
--
Roger
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
