CREATE TABLE Produs
(
    model NUMBER NOT NULL,
    fabricant VARCHAR2(30) NOT NULL,
    categorie VARCHAR2(30) NOT NULL,
    pret NUMBER NOT NULL
);

I have table Produs, where pret represents the price and moneda represents the currency which is 'RON' or 'EUR'. I have to select the model, fabricant (manufacturer in English) for the minimum price.

The problem is that I didn't know how to convert EUR to RON, and this is what I've written:

FROM Produs
WHERE pret < (SELECT MIN(pret) FROM produs WHERE pret=(
CASE
WHEN moneda='RON' THEN pret
WHEN moneda='EUR' THEN pret*5
ELSE pret
END) )

It works but shows me a lot of rows.

CodePudding user response：

Your current query is finding any rows where the pret value is less than the minimum value, using a comparison which will essentially exclude any EUR value unless there happens to be a row with a matching RON (x 5); so depending on the exact data you're doing to get the lowest RON value, and find any (unadjusted) values lower than that, which will all be EUR. Or if the lowest pret happens to be in RON, it won't find anything.

If you wanted all the columns, including the adjusted price, and you're on a fairly recent version of Oracle, you could do:

SELECT model, fabricant, categorie, moneda, pret,
  CASE moneda WHEN 'EUR' THEN pret * 5 ELSE pret END as adj_pret
FROM produs
ORDER BY adj_pret
FETCH FIRST 1 ROWS ONLY

or if you just want the model and fabricant (or any other original values, but not the adjust value) then:

SELECT model, fabricant
FROM produs
ORDER BY CASE moneda WHEN 'EUR' THEN pret * 5 ELSE pret END
FETCH FIRST 1 ROWS ONLY

fiddle

On older versions which don't support FETCH you can use a subquery and rownum or a ranking function to achieve the same thing.

If there are multiple rows with the same adjusted value then which you will see is indeterminate. You can either change ONLY to WITH TIES to show all of them, or add to the ORDER BY clause to make the result determinate - e.g. ORDER BY adj_pret, model to get whichever of the rows has the lowest ID.

I must use MIN()

You could get the minimum adjusted value in a subquery, which is sort of what you were attempting I think; but then you have to compare the adjusted value of each row with that too:

SELECT model, fabricant
FROM produs
WHERE CASE moneda WHEN 'EUR' THEN pret * 5 ELSE pret END = (
  SELECT MIN(CASE moneda WHEN 'EUR' THEN pret * 5 ELSE pret END)
  FROM produs
)

fiddle

That has the opposite default behaviour - if there are multiple rows with the same adjusted value then it will show them all, like WITH TIES. If you only ever want a single result then you'll need to add an order-by clause to and FETCH FIRST 1 ROW ONLY to get a determinate result.