CODEWITHSUNDEEP
sql
Steve G.
Steve G.

Reputation: 409

Order UNION results

my apologies because I know this is a duplicated question, but for some reason my brain isn't able to convert the answers to some of these other questions into my unique problem here.

I'm running a fiction site with information stored in an SQL database. One page has a bunch of characters listed. What I need to do is be able to list these characters by faction. But some don't have a faction, and in the SQL database, their faction is listed as 0. When ordering by faction, obviouisly I'm using ORDER BY Factionnumber. I want these factionless people to appear at the bottom, not at the top, but by ordering by faction number, since they're 0, they appear first. I'd love to be able to use an ORDER clause for each sub-statement, but that results in an SQL parsing error. So I have to order the results all at the same time.

So far, my SQL query is such (cut down to only a few field names for brevity's sake):

SELECT Id, Charactername, Faction FROM characters
WHERE Faction>0
 UNION
SELECT Id, Charactername, Faction
WHERE Faction=0 ORDER BY Faction, CharacterName

My problem is, even though I have the two statements joined by a UNION, I'm ordering by the faction at the end, so SQL is just taking the results of both statements and shuffling them together afterward anyway. How can I make the factionless characters show up at the bottom? I've taken a few of the statements in other answers here--for instance, using subqueries in parantheses, etc.--and tried to tweak them for my own unique situation, but I always run into a logic or parsing error that I can't quite seem to get around.

Does anyone have an elegant solution to this? Thanks!

If you need, I can provide the URL of the site, but I didn't want to at first since that seems like blatant self-promotion on a site that's not for that purpose.

Upvotes: 0

Views: 41

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1271141

I think you merely want the right order by:

SELECT Id, Charactername, Faction
FROM characters
ORDER BY (CASE WHEN faction > 0 THEN 1 ELSE 2 END), CharacterName;

No WHERE or UNION is necessary at all.

Upvotes: 0

Shoo Limberger
Shoo Limberger

Reputation: 386

You can do something like this:

SELECT Id, Charactername, Faction FROM characters
WHERE Faction>0
UNION
SELECT Id, Charactername, 999999999 as Faction FROM characters
WHERE Faction=0 ORDER BY Faction, CharacterName

The number in the second select must be biger then the max(Faction) then in your code you treat the 999999999 as empty!

You could use a case to!

SELECT Id, Charactername, Case When Faction=0 Then 9999999999 else Faction end AS Faction 
FROM characters
ORDER BY Faction, CharacterName

Upvotes: 1

Eray Balkanli
Eray Balkanli

Reputation: 8000

If you are using Sql Server, you can benefit from cte like:

;with cte (id,Charactername,Faction) as (
   SELECT Id, Charactername, Faction FROM characters
   WHERE Faction>0
   UNION
   SELECT Id, Charactername, Faction FROM characters
   WHERE Faction=0
)
select *
from cte
order by Faction, id, Charactername

If the usage of UNION is not mandatory, you can also try:

select id, Charactername, Faction
from characters
where Faction >= 0
order by Faction, id, Charactername

Upvotes: 0

Related Questions