Different books have different prices at different dealers. here we need to find out who is the highest distributor of each book? Find the lowest possible.
Different books have different prices at different dealers. here we need to find out who is the highest distributor of each book? Find the lowest possible.
Mysql> use test;
Database changed
Mysql> create table shop (
-> Article INT (4) unsigned zerofill default '100' not null,
-> Dealer CHAR (20) DEFAULT ''not null,
-> Price DOUBLE (16, 2) DEFAULT '0. 00' not null,
-> Primary key (article, dealer ));
Query OK, 0 rows affected (0.13 sec)
Mysql> insert into shop VALUES
-> (1, 'A', 3.45), (1, 'B', 3.99), (2, 'A', 10.99), (3, 'B ', 1.45 ),
-> (3, 'C', 1.69), (3, 'D', 1.25), (4, 'D', 19.95 );
Query OK, 7 rows affected (0.03 sec)
Records: 7 Duplicates: 0 Warnings: 0
Mysql> select * from shop;
+ --------- + -------- + ------- +
| Article | dealer | price |
+ --------- + -------- + ------- +
| 0001 | A | 3.45 |
| 0001 | B | 3.99 |
| 0002 | A | 10.99 |
| 0003 | B | 1.45 |
| 1, 0003 | C | 1.69 |
| 1, 0003 | D | 1.25 |
| 1, 0004 | D | 19.95 |
+ --------- + -------- + ------- +
7 rows in set (0.06 sec)
Mysql> select article, max (price) from shop group by article
->;
+ --------- + ------------ +
| Article | max (price) |
+ --------- + ------------ +
| 0001/3.99 |
| 0002/10.99 |
| 0003/1.69 |
| 0004/19.95 |
+ --------- + ------------ +
4 rows in set (0.05 sec)
Mysql> select article, max (price), dealer from shop group by article;
+ --------- + ------------ + -------- +
| Article | max (price) | dealer |
+ --------- + ------------ + -------- +
| 0001 | 3.99 | A |
| 0002 | 10.99 | A |
| 1, 0003 | 1.69 | B |
| 0004 | 19.95 | D |
+ --------- + ------------ + -------- +
4 rows in set (0.00 sec)
Mysql> select article, dealer, price from shop s1
-> Where price = (select max (s2.price) from shop s2
-> Where s1.article = s2.article );
+ --------- + -------- + ------- +
| Article | dealer | price |
+ --------- + -------- + ------- +
| 0001 | B | 3.99 |
| 0002 | A | 10.99 |
| 1, 0003 | C | 1.69 |
| 1, 0004 | D | 19.95 |
+ --------- + -------- + ------- +
4 rows in set (0.01 sec)
Mysql> select s1.article, dealer, s1.price
-> From shop s1
-> Join (
-> Select article, max (price) as price from shop
-> Group by article) as s2
-> On s1.article = s2.article and s1.price = s2.price;
+ --------- + -------- + ------- +
| Article | dealer | price |
+ --------- + -------- + ------- +
| 0001 | B | 3.99 |
| 0002 | A | 10.99 |
| 1, 0003 | C | 1.69 |
| 1, 0004 | D | 19.95 |
+ --------- + -------- + ------- +
4 rows in set (0.05 sec)
Mysql> select s1.article, s1.dealer, s1.price from shop s1
-> Left join shop s2 on s1.article = s2.article and s1.price select s1.article, s1.dealer, s1.price, s2. * from shop s1 left join shop s2
On s1.article = s2.article and s1.price
