Using correlated subqueries on 4.1 you could do it all in sql:
SELECT ProductId, MarketId, Type, Price
FROM Products a
WHERE 3>=(SELECT count(*) --finds highest prices
FROM Products b
where b.ProductId=a.ProductId
AND b.MarketId=a.MarketId
AND b.Type=a.Type
AND b.Price>=a.Price)
UNION --UNION ALL will remove duplicates between highest/lowest
SELECT ProductId, MarketId, Type, Price
FROM Products a
WHERE 3>=(SELECT count(*) --finds lowest prices
FROM Products b
WHERE b.ProductId=a.ProductId
AND b.MarketId=a.MarketId
AND b.Type=a.Type
AND b.Price<=a.Price)
ORDER BY ProductId, MarketId, Type, Price
Thanks,
Ed
>Subject: Re: three highest and lowest column values
>* 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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
