CODEWITHSUNDEEP
mysqlsqloracle-database
HC1122
HC1122

Reputation: 414

How to select item with the biggest price?

as you can see I am working in SQL Developer and trying to get some data out of my tables.

I'm trying to get out the item with its biggest price, but don't know how to manage it. For price I was trying

SELECT MAX(price) FROM price

but I get return 95€, when the biggest price is 350€.

Can someone help me, that I Will get the biggest price (from table Price) and the name of item (from table Item) ?

EDIT: Here are my tables, if that helps you

INSERT INTO ITEM VALUES (1, 'Item one', 1);
INSERT INTO ITEM VALUES (2, 'Item two', 1);
INSERT INTO ITEM VALUES (3, 'Item three', 2);
INSERT INTO ITEM VALUES (4, 'Item four', 2);
INSERT INTO ITEM VALUES (5, 'Item five', 3);
INSERT INTO ITEM VALUES (6, 'Item six', 3);

/* CREATE TABLE ITEM(
ID INTEGER NOT NULL,
NAME VARCHAR2(30) NOT NULL,
TK_ORG INTEGER NOT NULL
); */

INSERT INTO PRICE VALUES (1, 'normal', '50 €', 1);
INSERT INTO PRICE VALUES (2, 'special offer', '45 €', 1);
INSERT INTO PRICE VALUES (3, 'normal', '80 €', 2);
INSERT INTO PRICE VALUES (4, 'special offer', '150 €', 2);
INSERT INTO PRICE VALUES (5, 'normal', '40 €', 3);
INSERT INTO PRICE VALUES (6, 'special offer', '25 €', 3);
INSERT INTO PRICE VALUES (7, 'normal', '70 €', 4);
INSERT INTO PRICE VALUES (8, 'special offer', '45 €', 4);
INSERT INTO PRICE VALUES (9, 'normal', '95 €', 5);
INSERT INTO PRICE VALUES (10, 'special offer', '320 €', 5);

/* CREATE TABLE PRICE(
ID INTEGER NOT NULL,
PRICE_TYPE VARCHAR2(50) NOT NULL,
PRICE VARCHAR2(10) NOT NULL,
TK_ITEM INTEGER NOT NULL
); */

Upvotes: 0

Views: 501

Answers (3)

Saty
Saty

Reputation: 22532

You can remove € from your price and convert it to integer. Adding € means your price is not a number . Fist we convert it to number these we get max result 

 SELECT MAX(CONVERT(REPLACE(pricing,' €',''),UNSIGNED INTEGER)) FROM price

Upvotes: 0

mariobgr
mariobgr

Reputation: 2201

First, change the column type to integer. Right now, I assume, it is VARCHAR or TEXT. As it sorts by the first symbol, of course 9 > 3, that's why you get the weird results. Remove the € from the row's content and move it to another column.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269763

You would seem to be storing the price as a string and not as a number. In MySQL, you can use silent conversion to get what you want:

SELECT MAX(price + 0)
FROM price;

In Oracle, for this example, you can just do:

SELECT MAX(to_number(replace(price, '€', '')))
FROM price;

although a regular expression would be more flexible if you had more currencies.

Upvotes: 4

Related Questions