Hello.
I'm not sure about the speed of this query, but it seems to work:
mysql> source g.sql
+-------+-------+--------+
| id | price | vendor |
+-------+-------+--------+
| OG012 | 20 | b |
| OG013 | 40 | c |
+-------+-------+--------+
[EMAIL PROTECTED] mysql-debug-5.0.10-beta-linux-i686]$ cat g.sql
SELECT p4.id
,p4.price
,p4.vendor
FROM
(
SELECT p3.id
,p3.price
,p3.vendor
,MIN( p3.rating)
FROM (
SELECT p1.id
,p1.price
,p1.vendor
,q1.rating
FROM p p1
INNER JOIN q q1 ON p1.vendor = q1.vendor
WHERE p1.price = (
SELECT MIN(p2.price)
FROM p p2
WHERE p2.id = p1.id
)
) AS p3
GROUP BY p3.id, p3.price
) AS p4
;
See:
http://dev.mysql.com/doc/mysql/en/example-maximum-column-group-row.html
Joe Culler <[EMAIL PROTECTED]> wrote:
> Hello there,
>
> first of all, my english isn't good, hope you understand what I mean.
>
> I have a table name "p" like that:
>
> mysql> select * from p;
> +-------+-------+--------+
> | id | price | vendor |
> +-------+-------+--------+
> | OG012 | 40 | a |
> | OG012 | 20 | b |
> | OG012 | 20 | c |
> | OG013 | 40 | c |
> +-------+-------+--------+
>
> and I have another table name q:
> mysql> select * from q;
> +--------+--------+
> | vendor | rating |
> +--------+--------+
> | a | 1 |
> | b | 2 |
> | c | 3 |
> +--------+--------+
>
> My question is how do I find the minimal price for each id and vendor
> rating is highest.
> I wish my result is:
>
> +-------+-------+--------+
> | id | price | vendor |
> +-------+-------+--------+
> | OG012 | 20 | b |
> | OG013 | 40 | c |
> +-------+-------+--------+
>
> Since vender b and c are the same price for id(OG012), but vendor b has=20
> minimal rating then vendor c.
>
> Many thanks,
> Joe.
>
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
<___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
