show and mark some records on top

Question

My target DBMS's are both Oracle and MySQL

 SELECT
  users.*
  , hr_orders.position_label
  , hr_orders.is_boss
  , deps.label AS dep_label        -- preprocessor removes AS when using Orcale
  , users.id IN (?) AS show_on_top -- Orcale doesn't accept this IN thing
 FROM
  -- some joins here
 WHERE
  -- some filters

Is it possible to give cross-DBMS equvalent of this query?

UPDATE

Ok, it ruins the logic, the guy before me wanted always show some users on top,

WHERE
    users.fake = 0
AND (
        users.id IN (?)
    OR
        -- some more detailed filters
    )
ORDER BY
    IF (users.id IN (?), 1, 2), users.label

where ? is parameter referring to top users.

This show_on_top field is needed to highlight top records later.

Therefore, if I move IN to where clause, only users shown on top will be selected, not the rest.

Splitting the query into two and combining users list in code still looks ugly to me.

Answer 1

This should works:

WHERE users.id IN (?) AND 
  -- some filters

Answer 2

You should put the IN into the WHERE clause

SELECT
  users.*
  , hr_orders.position_label
  , hr_orders.is_boss
  , deps.label AS dep_label        
  , users.id  AS show_on_top 
 FROM
  -- some joins here
 WHERE
  -- some filters
  AND users.id IN (?,?,?)

Answer 3

It looks to me this query will not work for mysql too. You can use "IN" only in "WHERE" part of the query. In your example it is in the field list. Can you provide full query without your comments inside it?

Answer 4

If you want these rows on top, rather than just these rows, you just need to put them in the ORDER BY, not in the WHERE (or in the SELECT).

Don't know whether this will work on MySQL, but it should for Oracle

ORDER BY
    CASE WHEN users.id IN (?) then 1 else 2 end, users.label