I have a search query which yields a resultset based on input parameters and the result can be sorted (ASC/DESC) based on different parameters: price, duration etc (with pagination in place and limit of 10 records)
I now have a requirement wherein, if I have an id passed in, I'd like the corresponding record to be made to sticky at the top in the given resultset.
Lets say we have a package table like this:
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"
Let's assume the complete resultset is 20 records with ids 1 to 20. I'm now required to return record with id 14 to always be at the top. I came up with the following query, but this isn't working:
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 as to why this is not working: After order by clause, the resultset is sorted in descending order, which is then truncated at 10 records. The record with id=14 may not be a part of this truncated set. Is this correct ?
How do I get record with id=14 to stick at the top ?