SELECT only TOP value using WHERE clause

Question

In my SQLFiddle Here

I'm trying to SELECT ROWs WHERE i.id = 1 with the highest value, Then i.id = 2 and so on.

So here in my Query

SELECT
i.`item`, v.`nom`, v.`value`
FROM `items` i
LEFT JOIN `values` v ON v.`item_id` = i.`id`
WHERE
i.`id` IS NOT NULL 
  AND
CASE
  WHEN i.`id` = 1 THEN 1
  WHEN i.`id` = 2 THEN 2
  ELSE 3
END
ORDER BY 
CASE
  WHEN i.`id` = 1 THEN 1
  WHEN i.`id` = 2 THEN 2
  ELSE 3
END
, v.`value` DESC;

I get the rows

item    nom     value
C1      C12     7
C1      C11     4
C2      C21     9
C2      C22     8
C4      C41     44
C4      C42     13
C5      C52     12
C5      C51     8
C3      C31     3
C3      C32     2

But what I want to get is only the TOP values, So it becomes

item    nom     value
C1      C12     7
C2      C21     9
C4      C41     44

To clear it more, I want to SELECT each TOP values for each i.id,

Then I SELECT after SELECTing the 3 specific i.ids the Highest TOP value in the Table itself,

Just in case none of the 3 i.ids exists.

How can I do that? Is the Query overall Good or Bad? Can I optimize it more?

I'm not trying to retrieve the Last Record by ID, But the highest record by value, Something like

SELECT
i.`item`, v.`nom`, v.`value`
FROM `items` i
LEFT JOIN `values` v ON v.`item_id` = i.`id`
WHERE
i.`id` IS NOT NULL 
  AND
CASE
  WHEN i.`id` = 1 AND TOP(v.`value`) THEN 1
  WHEN i.`id` = 2 AND TOP(v.`value`) THEN 2
  ELSE TOP(v.`value`) 3
END
ORDER BY 
CASE
  WHEN i.`id` = 1 THEN 1
  WHEN i.`id` = 2 THEN 2
  ELSE 3
END

SELECT only TOP value using WHERE clause

0 Answers0