SQL Query: finding cheapest car by company

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?

Answer

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

---

View on DB Fiddle