Avatar of Web IT
Web IT
 asked on

Order By Alias two columns

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.
MySQL ServerSQL

Avatar of undefined
Last Comment
Web IT

8/22/2022 - Mon
Ryan Chong

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

Open in new window

Peter Chan

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 Pletcher

SELECT ...
...
ORDER BY alias1Title DESC, alias2Title
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Web IT

ASKER

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 Pletcher

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??


Web IT

ASKER

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   

Open in new window

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Ryan Chong

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 Pletcher

Then:


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

ASKER CERTIFIED SOLUTION
Web IT

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question