Make records rise to the top - sorting 1

[drmaves]

I'm trying to get a sub group of records to display first before the rest of the group.

For example, I make a selection of real estate properties and the result is 22 records. The records are ordered by 'price'. Two of these records, which belong to a partcular agent, I want to have show up above the others. There is a column in the real estate property table that holds the agent's id so I know what two records belong to this agent.

It's been suggested that I try the following but the CASE segment is causing a syntax error. Is it possible to make this work or is there a better way to approach this?

SELECT idx_res.*, CASE WHEN idx_res.agent_id = 1897 THEN 0 ELSE 1 END AS orderfield
FROM idx_res
WHERE idx_res.area = 'DSE'
ORDER BY orderfield, price

 

[ericbrunson]

Are you saying the given select doesn't work? It looks like it should.

However, remember that you don't need to select the field you are ordering by, so try this:

SELECT   *
FROM     idx_res
WHERE    idx_res.area = 'DSE'
ORDER BY CASE WHEN idx_res.agent_id = 1897 THEN 0 ELSE 1 END, price

I do something similar in one of my applications and it works fine.

 

[cLFlaVA]

Couldn't you simplify your SELECT statement as follows:

[tt]
SELECT *
FROM idx_res
WHERE area = 'DSE' AND
agent_id = 1897
ORDER BY price
[/tt]

*cLFlaVA
----------------------------
A pirate walks into a bar with a huge ship's steering wheel down his pants.
The bartender asks, "Are you aware that you have a steering wheel down your pants?"
The pirate replies, "Arrrrr! It's driving me nuts!

 

[drmaves]

Eric's solution worked great! I just needed to move the CASE from the SELECT to ORDER BY.

Thanks Eric.