How to ensure that a record is always at the top of a given result set in mysql?

Question

I have a search query that gives a set of results based on input parameters, and the result can be sorted (ASC / DESC) based on different parameters: price, duration, etc. (paginated in place and limited to 10 entries)

Now I have a requirement in which, if I have an id , I would like the corresponding entry to be made for sticky at the top in this result set.

Suppose we have a package table as follows:

 Package - id - name - mPrice - vPrice - duration // Searching pkg based on Price (in DESC order) where name = Thailand sqlQuery = "SELECT p.id, p.name, p.mPrice, p.vPrice FROM package p WHERE p.name = LOWER('Thailand') ORDER BY (p.mPrice + p.vPrice) DESC LIMIT 10" 

Suppose that the complete result set is 20 records with identifiers from 1 to 20. Now I need to return a record with identifier 14 to always be at the top. I came up with the following query, but this does not work:

 sqlQuery = "SELECT p.id, p.name, p.mPrice, p.vPrice FROM package p WHERE p.name = LOWER('Thailand') or p.id = 14 ORDER BY CASE WHEN p.id=14 then 0 else (p.mPrice + p.vPrice) end DESC LIMIT 10" 

My guess is why this does not work: after the order by clause, the result set is sorted in descending order, which is then truncated to 10 records. A record with id = 14 cannot be part of this truncated set. Is it correct?

How to get record with id = 14 to insert up?

Answer 1

 ORDER BY (p.id=14) DESC, (p.mPrice=p.vPrice) DESC 

p.id=14 returns 1 if the condition is true, 0 otherwise; therefore, sorting in descending order results in the desired line at the top.

Returning a number from a comparison is a MySQL function, with standard SQL that you would write:

 ORDER BY CASE WHEN (p.id=14) THEN 0 ELSE 1 END, CASE WHEN (p.mPrice=p.vPrice) THEN 0 ELSE 1 END 

---

It’s easier for me to read than UNION , and it can work better.

Answer 2

Use union to always have your package at the top:

 SELECT id, name, mPrice, vPrice FROM package WHERE id = 14 UNION SELECT p.id, p.name, p.mPrice, p.vPrice FROM package p WHERE p.name = LOWER('Thailand') or p.id = 14 ORDER BY (p.mPrice = p.vPrice) DESC LIMIT 9 

Answer 3

try the following:

 order by case when p.id=14 then then (select MAX(mPrice)+1 from package ) else (p.mPrice = p.vPrice) end desc LIMIT 10 

Instead (select MAX (p.mPrice) +1 from the package) you can specify a constant value that will always be the maximum value, something like this

  order by case when p.id=14 then then 1000000 else (p.mPrice = p.vPrice) end desc LIMIT 10 

which would be more effective