CODEWITHSUNDEEP
mysqlsql
Dex
Dex

Reputation: 45

Sort order of each select statement in a mysql union

I have some trouble with the sort order of my selects in my union statement. I want to sort each select like firs for the name and second for the id.

(SELECT
  id,
  name
FROM
  table1
WHERE
  active=1
ORDER BY name)
UNION
(SELECT
   id,
   name
FROM
   table2
WHERE
   active=1
ORDER BY id)

Everything work fine without the where clause, but this is necessary for my query. How can i do different sorts for each select?

table1:

ID NAME
1 First
3 Second
2 Third

table2:

ID NAME
1 First1
2 Second1
3 Third1

result table1+table2:

ID NAME
1 First
3 Second
2 Third
1 First1
2 Second1
3 Third1

Upvotes: 0

Views: 59

Answers (1)

Ali Fidanli
Ali Fidanli

Reputation: 1372

Try this:

Select id,name from 
(
Select * from (
SELECT
  t.id,
  t.name,
  @rownum := @rownum + 1 as row_number
FROM
  test t
 cross join (select @rownum := 0) r
WHERE
  t.active=1
 order by t.name  ) tbl1

union all

select * from 
(
SELECT
  t.id,
  t.name,
  @rownum := @rownum + 1 as row_number
FROM
  test2 t
 cross join (select @rownum := 0) r
WHERE
  t.active=1
 order by t.id 
  ) tbl2
  ) x order by row_number

you should use union all

https://www.db-fiddle.com/f/dzBHKuzGmaQC8EmyuWcH2Y/2

Upvotes: 1

Related Questions