Following YouTube tutorial "Learn PostgreSQL Tutorial - Full Course for Beginners", I replicate teacher's code but yields different result and cannot figure out why.
Table is this simple:
id | make | model | price
----- ------------ -------------- ------------
1 | Toyota | Matrix | 25451.36
and so on, 1000 entries.
Querying cheapest model from manufacturer, tutorial says:
SELECT
make, model, MIN(price)
FROM
car
GROUP BY
make, model;
And it works properly, returning as many entries as distinct car makers. But when I run it in my PostgreSQL terminal returns all 1000 entries disordered.
However, when I query without model's name, I get the right answer, but (obviously) without the model name as shown below:
make | cheapest
--------------- ----------
Fillmore | 72263.48
McLaren | 78510.84
Any suggestions as to why this might happen?
CodePudding user response:
This db-fiddle works as expected. Notice the output. It shows a proper GROUP BY
.
Query source:
CREATE TABLE t (
make varchar(40),
model varchar(40),
price integer
);
INSERT INTO t (make, model, price) VALUES
('Fillmore', 'F_M1', 10000),
('Fillmore', 'F_M2', 20000),
('McLaren', 'M_M2', 40000),
('McLaren', 'M_M2', 60000),
('Toyota', 'T_M1', 12000),
('Toyota', 'T_M2', 24000),
('Toyota', 'T_M3', 48000);
SELECT
make, model, MIN(price)
FROM
t
GROUP BY
make, model
ORDER BY make, model;
Result:
Schema (PostgreSQL v10.0)
CREATE TABLE t (
make varchar(40),
model varchar(40),
price integer
);
INSERT INTO t (make, model, price) VALUES
('Fillmore', 'F_M1', 10000),
('Fillmore', 'F_M2', 20000),
('McLaren', 'M_M2', 40000),
('McLaren', 'M_M2', 60000),
('Toyota', 'T_M1', 12000),
('Toyota', 'T_M2', 24000),
('Toyota', 'T_M3', 48000);
Query #1
SELECT
make, model, MIN(price)
FROM
t
GROUP BY
make, model
ORDER BY make, model;
make | model | min |
---|---|---|
Fillmore | F_M1 | 10000 |
Fillmore | F_M2 | 20000 |
McLaren | M_M2 | 40000 |
Toyota | T_M1 | 12000 |
Toyota | T_M2 | 24000 |
Toyota | T_M3 | 48000 |