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 got the output
id | make | model | price
946 | Mazda | B-Series | $9937.75
I know that there are more expensive cars than this in the data set (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;