Code:
--------------
mysql Ver 14.12 Distrib 5.0.37, for mandriva-linux-gnu (i586) using readline 5.2
Connection id: 86
Current database: prodavnica
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.0.37 Mandriva Linux - MySQL Standard Edition (GPL)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: latin1
Conn. characterset: latin1
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 1 hour 31 min 30 sec
Threads: 1 Questions: 119 Slow queries: 0 Opens: 15 Flush tables: 1 Open tables: 9 Queries per second avg: 0.022
--------------
--------------
mysql Ver 14.12 Distrib 5.0.37, for mandriva-linux-gnu (i586) using readline 5.2
Connection id: 86
Current database: prodavnica
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.0.37 Mandriva Linux - MySQL Standard Edition (GPL)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: latin1
Conn. characterset: latin1
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 1 hour 31 min 30 sec
Threads: 1 Questions: 119 Slow queries: 0 Opens: 15 Flush tables: 1 Open tables: 9 Queries per second avg: 0.022
--------------
Izgled tabele prodavnica:
Code:
+---------+-----------+--------+
| artikal | dobavljac | cijena |
+---------+-----------+--------+
| 0001 | A | 3.45 |
| 0001 | B | 3.99 |
| 0002 | A | 10.99 |
| 0003 | B | 1.45 |
| 0003 | C | 1.69 |
| 0003 | D | 1.25 |
| 0004 | D | 19.95 |
+---------+-----------+--------+
+---------+-----------+--------+
| artikal | dobavljac | cijena |
+---------+-----------+--------+
| 0001 | A | 3.45 |
| 0001 | B | 3.99 |
| 0002 | A | 10.99 |
| 0003 | B | 1.45 |
| 0003 | C | 1.69 |
| 0003 | D | 1.25 |
| 0004 | D | 19.95 |
+---------+-----------+--------+
Pokusao sam napraviti upit koji bi mi iz tabele vratio najskupljeg dobavljaca za arikal '0003'.
Code:
mysql> SELECT artikal, dobavljac, MAX(cijena) FROM prodavnica WHERE artikal = '0003' GROUP BY artikal;
+---------+-----------+-------------+
| artikal | dobavljac | MAX(cijena) |
+---------+-----------+-------------+
| 0003 | B | 1.69 |
+---------+-----------+-------------+
1 row in set (0.00 sec)
mysql> SELECT artikal, dobavljac, MAX(cijena) FROM prodavnica WHERE artikal = '0003' GROUP BY artikal;
+---------+-----------+-------------+
| artikal | dobavljac | MAX(cijena) |
+---------+-----------+-------------+
| 0003 | B | 1.69 |
+---------+-----------+-------------+
1 row in set (0.00 sec)
Dobijam tacnu cijenu, ali pogresnu oznaku dobavljaca, umjeto B bi trebalo stajati C. Mozda sam pogresno grupisao ili sta vec. Znam da se ovo moze rijesiti i na drugi nacin ...
Code:
mysql> SELECT artikal,dobavljac,cijena FROM prodavnica WHERE artikal = '0003' ORDER BY cijena DESC LIMIT 1;
+---------+-----------+--------+
| artikal | dobavljac | cijena |
+---------+-----------+--------+
| 0003 | C | 1.69 |
+---------+-----------+--------+
1 row in set (0.00 sec)
mysql> SELECT artikal,dobavljac,cijena FROM prodavnica WHERE artikal = '0003' ORDER BY cijena DESC LIMIT 1;
+---------+-----------+--------+
| artikal | dobavljac | cijena |
+---------+-----------+--------+
| 0003 | C | 1.69 |
+---------+-----------+--------+
1 row in set (0.00 sec)
Ali kako preko MAX() i GROUP BY. ?
Hvala !
if (argc > 1 && strcmp(argv[1], "-advice") == 0) {
printf("Don't Panic!n");
exit(42);
}
printf("Don't Panic!n");
exit(42);
}