We help IT Professionals succeed at work.

Order By Alias two columns

Web IT
Web IT asked
on
This query gets two request in one table and two columns (one is alias1Title & alias2Title)

SELECT alias_name1.post_id AS alias1Title,alias_name2.post_id AS alias2Title
FROM wp_postmeta AS alias_name1, wp_postmeta AS alias_name2
WHERE  alias_name1.post_id=alias_name2.post_id
ORDER BY CASE WHEN alias1Title = alias1Title  THEN END DESC

Open in new window


If I want to sort  alias1Title  DESC  and alias2Title  ASC how do I do , I tried various AND statments but get syntax errors.
Comment
Watch Question

Ryan ChongSoftware Team Lead

Commented:
If I want to sort  alias1Title  DESC  and alias2Title  ASC how do I do , I tried various AND statments but get syntax errors.

try this instead:


 ORDER BY alias_name1.post_id DESC, alias_name2.post_id
HuaMin ChenProblem resolver

Commented:
Try
SELECT alias_name1.post_id AS alias1Title,alias_name2.post_id AS alias2Title
FROM wp_postmeta AS alias_name1, wp_postmeta AS alias_name2
WHERE  alias_name1.post_id=alias_name2.post_id
ORDER BY alias_name1.post_id desc, alias_name2.post_id asc

Open in new window

Scott PletcherSenior DBA
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
SELECT ...
...
ORDER BY alias1Title DESC, alias2Title

Author

Commented:

Tried scots solution like this (maybe missing somthing) and this simply spits out the two columns (or ASC of DESC )

it sorts the order of both (it is the same table maybe has somthing to do with this).


SELECT alias_name1.post_id AS alias1Title,alias_name2.post_id AS alias2Title
FROM wp_postmeta AS alias_name1, wp_postmeta AS alias_name2
WHERE  alias_name1.post_id=alias_name2.post_id  
ORDER BY alias1Title ASC, alias2Title

 I will also try to comment on other solutions later.

Scott PletcherSenior DBA
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:

I'm not sure what you mean.


You can sort using an alias column name.  So you just need to list alias1Title and/or alias2Title and whether you want DESC for each (ASC will be assumed if you don't explicitly specify DESC).  SQL will sort the result set as specified.


Do you want to dynamically specify the column(s) you sort on??


Author

Commented:

Thank you Scott ,Ryan and HuaMin!

To further clarify bellow is the latest query.

the result is :

alias1Title|alias2Title

     1           |         1

     2           |          2

     3           |          3    


What I am trying to achive is :

alias1Title|alias2Title

     1           |          3

     2           |          2

     3           |          1    


I want to have separate control of the columns which are based from the same table.

As mentioned before here is the query which quries the first table example above.



SELECT alias_name1.post_id AS alias1Title,alias_name2.post_id AS alias2Title 
FROM wp_postmeta AS alias_name1, wp_postmeta AS alias_name2 
WHERE  alias_name1.post_id=alias_name2.post_id 
ORDER BY alias_name1.post_id desc, alias_name2.post_id asc   
 
Ryan ChongSoftware Team Lead

Commented:

the result is :

alias1Title|alias2Title

     1           |         1

     2           |          2

     3           |          3    


What I am trying to achive is :

alias1Title|alias2Title

     1           |          3

     2           |          2

     3           |          1 

You can't really do by simply using the Order By clause. that's basically "modify" the data generated. you need to have the proper data before you can sort it properly.

to have a better understanding of your data, pls provide some sample data.

Scott PletcherSenior DBA
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:

Then:


SELECT ...
...
ORDER BY alias1Title, alias2Title DESC

Commented:

I will close this question.