0

please let us know how to use order by clause in sql union query

My query 

   select  receiverid as new_id, time from messenger where senderid= '".$_SESSION['id']."'
    UNION
  select senderid, time  from messenger where receiverid  = '".$_SESSION['id']."'

i need exactly Order by time Desc

Please help us I have tried many ways but not working.. Thank you in advance

CforCODE
  • 105
  • 6
  • 1
    **WARNING**: Whenever possible use **prepared statements** to avoid injecting arbitrary data in your queries and creating [SQL injection bugs](http://bobby-tables.com/). These are quite straightforward to do in [`mysqli`](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and [PDO](http://php.net/manual/en/pdo.prepared-statements.php) where any user-supplied data is specified with a `?` or `:name` indicator that’s later populated using `bind_param` or `execute` depending on which one you’re using. – tadman Oct 24 '19 at 17:32
  • @CforCODE . . . Although this is marked as a duplicate, the accepted answer is simply more complicated than unnecessary. I'm pondering whether I should reopen this question. – Gordon Linoff Oct 24 '19 at 17:49
  • @GordonLinoff the duplicate link contains many answers one of which is the same as the one you posted. – forpas Oct 24 '19 at 18:02

2 Answers2

0

This should work:

select receiverid as new_id
from messenger
where senderid= '".$_SESSION['id']."'
union
select senderid 
from messenger
where receiverid  = '".$_SESSION['id]
order by new_id;

More importantly, you don't need union. Just use:

select distinct (case when receiverid = ? then senderid else receiverid) as id
from messenger m
where ? in (receiverid, senderid)
order by id;

And the most important part here is using parameters. Don't munge query strings with parameter values.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I think that will only order the second `SELECT`, not everything. – Barmar Oct 24 '19 at 17:34
  • @Barmar . . . It works when I try it: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=f29f7255a41a023b0c1c560b5bf20979. Further, to order the individual `select`s, I know that you need parentheses. This is in contract to SQL Server, where a subquery *is* necessary to order unions. – Gordon Linoff Oct 24 '19 at 17:35
  • OK, I frequently get confused about whether it applies to the last query or the whole union. – Barmar Oct 24 '19 at 18:00
  • @Gordon Linoff tq sir –  CforCODE Oct 24 '19 at 22:29
-1

If you want to order the final result, you need to put the UNION in a subquery.

And you need to select the column that you want to order by.

SELECT *
FROM (
    select  receiverid as new_id, time  from messenger where senderid= '".$_SESSION['id']."'
    UNION
    select senderid, time  from messenger where receiverid  = '".$_SESSION['id']."'
) AS x
ORDER BY time
Barmar
  • 741,623
  • 53
  • 500
  • 612