MySQL queries Several SQL statements of the most expensive book dealers

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