expert on "order by" - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

expert on "order by"

Author

Topic

leracj
Starting Member

13 Posts

Posted - 2013-06-29 : 13:26:38

i have a table here:

i need to "order it by first_name"
so that when i run a query like

SELECT meta_value FROM wp_usermeta

where the top of the result will be all names in ORDER BY ASC

please help

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-06-29 : 14:01:44

[CODE]

SELECT meta_value FROM wp_usermeta
ORDER BY (CASE WHEN meta_Key = 'first_name' THEN 1 ELSE 2 END),
meta_value;

[/CODE]

leracj
Starting Member

13 Posts

Posted - 2013-06-29 : 14:28:11

what does THEN 1 ELSE 2 mean?

quote:
Originally posted by MuMu88

[CODE]

SELECT meta_value FROM wp_usermeta
ORDER BY (CASE WHEN meta_Key = 'first_name' THEN 1 ELSE 2 END),
meta_value;

[/CODE]

please help

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-06-29 : 14:54:16

The case statement returns 1 if the meta_key is 'first_name' else it returns 2.
When you pass these values to your order by clause it sorts the data with 'first_name' followed by all other data.

leracj
Starting Member

13 Posts

Posted - 2013-06-29 : 15:37:54

how about if i want to insert another row

basically i want to have a row for first name and another row for address
how do i do that?

my code:
SELECT DISTINCT ID, user_login, user_email, meta_value, COUNT( * ) , meta_key
FROM wp_users um, wp_usermeta u
WHERE (
user_login LIKE '%tho%'
OR meta_value LIKE '%tho%'
OR user_email LIKE '%tho%'
)
AND (
user_id = ID
AND (
meta_key = 'first_name'
OR meta_key = 'last_name'
OR meta_key = 'last_name'
OR meta_key = 'city'
OR meta_key = 'address'
OR meta_key = 'programs'
)
)
GROUP BY ID, user_login, user_email
HAVING COUNT( * ) >0
ORDER BY (

CASE meta_key
WHEN 'first_name'
THEN 1
ELSE 100
END
) ASC , meta_key ASC , meta_value
LIMIT 0 , 30

please help

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-06-30 : 23:37:09

You are using MySQL ? for MySQL question, please post at dbforums.com

SQLTeam.com is on Microsoft SQL Server.

KH
[spoiler]Time is always against us[/spoiler]

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-07-01 : 00:29:57

quote:
Originally posted by leracj

how about if i want to insert another row

basically i want to have a row for first name and another row for address
how do i do that?

my code:
SELECT DISTINCT ID, user_login, user_email, meta_value, COUNT( * ) , meta_key
FROM wp_users um, wp_usermeta u
WHERE (
user_login LIKE '%tho%'
OR meta_value LIKE '%tho%'
OR user_email LIKE '%tho%'
)
AND (
user_id = ID
AND (
meta_key IN ( 'first_name', 'last_name', 'city', 'address', 'programs'))
)
GROUP BY ID, user_login, user_email -- include these columns(meta_value, meta_key) in GROUP BY cluase to work out the query
HAVING COUNT( * ) >0
ORDER BY user_id, (
CASE meta_key
WHEN 'first_name'
THEN 1
ELSE 100
END
) ASC , meta_key ASC
LIMIT 0 , 30

please help

try out the above modified query...
>>how about if i want to insert another row?
Can you provide expected output?

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources