1

Cars table

This is my cars table which has 1000 rows. Each of make or manufacturer has different model and same model can have different price. I have to output min price among all of the model of each manufacturer and i cant think of any way to do this. Nearest to what i got is,

SELECT make, model, min(price) FROM car
GROUP BY model, make
ORDER BY make;

which outputs,527 rows

enter image description here

But i want min price among all of the models of each make. HELP!!

Mayank Pant
  • 145
  • 1
  • 8
  • 2
    Sample data is better presented as [formatted text](https://meta.stackoverflow.com/a/251362). See [here](https://meta.stackexchange.com/questions/81852) for some tips on how to create nice looking tables. –  Sep 11 '19 at 15:10
  • 1
    Beside to what @a_horse_with_no_name said, please do not use the `database` tag unless you have a question about database design. – Ilyes Sep 11 '19 at 15:12
  • Possible duplicate of [Select first row in each GROUP BY group?](https://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group) – Clockwork-Muse Sep 11 '19 at 17:18

5 Answers5

2

In Postgres, I recommend distinct on for this purpose:

select distinct on (make) c.*
from cars c
order by make, price asc;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • This type of SQL command is new for me. Can u please guide me more on where to learn these type of command. – Mayank Pant Sep 11 '19 at 16:46
  • @MayankPant . . . `DISTINCT ON` is described in the documentation: https://www.postgresql.org/docs/current/sql-select.html#SQL-DISTINCT. – Gordon Linoff Sep 11 '19 at 18:14
2

From your question it seems that you want minimum price of each make. The following query can help in my opinion.

SELECT make,MIN(price) from car
group by make
order by make;
Anirudh
  • 652
  • 6
  • 12
1

Assuming you need "the lowest priced Model for each Make", you can easily get the lowest price per Make according to the answers given, but that will not give you the Model name!

I'd suggest using a window function to rank the data, partitioning by Make, and ordering by Price ascending. Then simply select all rows ranked 1.

There may be a possibility that two Models within a Make may be equally low priced. In that case, you'd have two rows returned for that Make. If that's a possibility and also a problem, you'd have to engage in further processing to decide how to break the tie or consolidate the row into one (for example, by concatenating the Model names).

Steve
  • 950
  • 7
  • 11
1

Use rank() window function inside a CTE to filter the minimum prices: 

with cte as (
  select *, 
    rank() over (partition by model order by price) rn 
  from car
)
select id, make, model, price
from cte
where rn = 1
order by make;

This will return ties in minimum price.
If you don't need ties replace rank() with row_number().
I assumed that a model name cannot be used by 2 makers. If this is not the case then change to this:

rank() over (partition by make, model order by price) rn
forpas
  • 160,666
  • 10
  • 38
  • 76
  • This type of SQL command is new for me. Can u please guide me more on where to learn these type of command. Any reference. – Mayank Pant Sep 11 '19 at 16:49
  • You can read the official documentation for window functions: https://www.postgresql.org/docs/9.1/tutorial-window.html and for CTEs: https://www.postgresql.org/docs/9.1/queries-with.html – forpas Sep 11 '19 at 16:51
  • Forpas, you want to partition by Make in this case - the Models within each Make will then be ranked according to price. – Steve Sep 11 '19 at 16:54
  • @Steve now I see what you mean, but I assume that the model names are unique (meaning a model name cannot be used by another maker), right? So no need of another level of partition. – forpas Sep 11 '19 at 16:58
  • @Forpas, I suppose we need him to clarify, but partitioning by model (assuming no two makes have a same-named model) will only filter out the higher-priced "duplicates" for each model - it won't find the cheapest model per make (which is how I inferred his requirement). If he did require the latter, then you would partition on Make and Model to avoid the possibility of filtering same-named models across two different makes (a remote possibility with cars I concede, but the potential is there - for example, the Jaguar 420, a very old car, and the BMW 420, a modern car). – Steve Sep 11 '19 at 17:01
  • @Steve if only 420 can stand as a model then you are right. I will add that in the partition as a potential solution. – forpas Sep 11 '19 at 17:05
0

I think you are looking for a simple left join.

select t1.make, t1.model, t2.min_price 
from car t1 left join (select make, min(price) min_price from car group by make) t2 on 
t1.make=t2.make
Radagast
  • 5,102
  • 3
  • 12
  • 27