SQL MAX and MIN functions-CodePudding

I made a database called cars. The table consists of 4 columns: (id, make, model, price). I want to find the car with the highest price. I ran this code in SQL shell,

SELECT id, make, model, price
FROM cars
WHERE price = (SELECT MAX(PRICE) FROM cars);

and get the output

id | make  |  model   |  price

946 | Mazda | B-Series | $9937.75

I know that there are more expensive cars than this (ex. $49437.07, $21305.93 . . . ).

I have version 14 of PostgreSQL installed. Can anyone explain what is going wrong here?

CodePudding user response：

The following code will convert the date type to numeric
alter table cars alter column price numeric USING (price::numeric);

then your code would work.

if you have null values in price then you would have to use:

alter table cars alter column price numeric using nullif(price,'')::numeric;

CodePudding user response：

No, but I would write

select * from cars order by price desc limit 1;