CODEWITHSUNDEEP
mysqlsql
Mediator
Mediator

Reputation: 15378

How to sort this query

I have mysql query and I need order by timeaccept

    SELECT id, name, level as lvl, leader as par3
FROM Road
WHERE Road.id LIKE '180' 
UNION ALL SELECT id, name, lvl, lastonline, dnd, null, raceid, currenthp, dressed_hp as hp 
FROM Users 
WHERE rid LIKE '180' AND id NOT LIKE (SELECT leader FROM Road WHERE id LIKE '180')
UNION ALL 
SELECT Users.id as id, Users.name, lvl, lastonline, dnd, currentlocid, Location.name as locname, currenthp, dressed_hp as hp 
FROM Users LEFT JOIN Location ON (Location.id = currentlocid) 
WHERE Users.id LIKE(SELECT leader FROM Road WHERE id LIKE '180') ORDER BY Users.timeacceptinvite

Upvotes: 0

Views: 61

Answers (2)

Andomar
Andomar

Reputation: 238086

An order by applies to the entire union. You can't specify an alias like Users.timeacceptinvite that's only used in part of the union. You have to specify the name of the column as defined in the topmost part of the union.

Having said that, the various queries that make up your union have a different amount of columns. That will give a syntax error of its own. As a first step, ensure that all parts of the union return the same number of columns, with the same types.

Upvotes: 2

Subdigger
Subdigger

Reputation: 2193

select x.* from (
  SELECT
    id, name, level as lvl, leader as par3, timeacceptinvite
  FROM Road
  WHERE Road.id LIKE '180' 
  UNION ALL
  SELECT
    id, name, lvl, lastonline, dnd, null, raceid, currenthp, dressed_hp as hp, timeacceptinvite 
  FROM Users 
  WHERE rid LIKE '180'
    AND id NOT LIKE (SELECT leader FROM Road WHERE id LIKE '180')
  UNION ALL 
  SELECT
   Users.id as id, Users.name, lvl, lastonline, dnd, currentlocid, Location.name as locname, currenthp, dressed_hp as hp, timeacceptinvite 
  FROM Users LEFT JOIN Location ON (Location.id = currentlocid) 
  WHERE Users.id LIKE(SELECT leader FROM Road WHERE id LIKE '180')
) as x
 ORDER BY x.timeacceptinvite

edit 1

but i think that you will receive different number of columns error...

Upvotes: 1

Related Questions