CODEWITHSUNDEEP
sqlite
user34537
user34537

Reputation:

order by 2 columns with sqlite

I'd like to order by names however the creator should be listed first so I wrote the below and got the error

1st ORDER BY term does not match any column in the result set

How do I write this so the creator is the first result? I don't understand why I have this error. My query is

select u.id as id, u.name as name, 0 as creator from User u 
join Moderator m on m.forumID=@forumID and m.userID=u.id 
union 
select u.id, u.name, f.creator as creator from User u 
join Forum f on f.id=@forumID and u.id=w.creator 
order by creator<>0, u.name

Upvotes: 0

Views: 582

Answers (2)

itmitica
itmitica

Reputation: 511

select u.id as id, u.name as name, 0 as creator from User u 
join Moderator m on m.forumID=@forumID and m.userID=u.id 
union 
select u.id, u.name, f.creator from User u 
join Forum f on f.id=@forumID and u.id=w.creator 
order by creator, name

In an union, the first query gives the column names, the following queries only have to return matched column type values, their names is not important.

Upvotes: 3

Fabio
Fabio

Reputation: 32455

You cannot use alias name creator in the WHERE, JOIN and ORDER BYstatements.

Use number of column in your case:

SELECT ....
ORDER BY 3, 2

this is not good practice but it works in your question...

Upvotes: 3

Related Questions