Home > Enterprise >  SQL Query: finding cheapest car by company
SQL Query: finding cheapest car by company

Time:02-20

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

View on DB Fiddle

  • Related