Solved

SQL ORDER BY CASE problem

Posted on 2008-10-28
7
390 Views
Last Modified: 2012-05-05
Im having trouble with a ORDER in my sql statement.
in my DB i have table "flaw" with fields:
"area_nr" with values:
P1-1, P1-1, P1-1, P1-2, P1-2, P1-2, P1-3, P1-3, P1-3, P1-10
...and "id" with values:
2, 1, 1, 1, 2, 2, 3, 1, 1, 3

I get the usual problem with P1-10 coming out before P1-2. I have tried many differet ways but cant seem to make i work. What i want is to order as follow:
"area_nr" : "id"

P1-1 : 1
P1-1 : 1
P1-1 : 2

P1-2 : 1
P1-2 : 2
P1-2 : 2

P1-3 : 1
P1-3 : 1
P1-3 : 3

P1-10 : 3

My ORDER code so far is:

$order = "CASE WHEN CAST(area_nr AS SIGNED) = 0 THEN 1 ELSE 0 END, CAST(area_nr AS SIGNED) ASC,";
$order .= "CASE WHEN CAST(id AS SIGNED) = 0 THEN 1 ELSE 0 END, CAST(id AS SIGNED) ASC, area_nr, id";

and so on...

But this doesnt do it... Anyone expert in SQL and who can give me a clue or rewrite my statement for me.
0
Comment
Question by:m_mlynek
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22820667
If the value is always "P1-??", then you can just can just take the numberical part.
+Couple of ways to get right portion of string.  I chose to reverse the string and search for the first "-" and then take the right of the original string that number of characters - 1.
+Cast the result as INT
$order = "CAST(RIGHT(area_nr, INSTR(REVERSE(area_nr),'-')-1) AS INT ASC, id";

Open in new window

0
 
LVL 1

Author Comment

by:m_mlynek
ID: 22821591
The area_nr is not always P1-?? so i cant do that. It can be 1-Apartment, Apartment-A and so on. So i cant always be sure that the last is an INT. On top of that i also needed both fields sortet, first area_nr and within every area_nr, id sorted. Any other idea?
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22821661
Can you give some sample of data and the sorting rules you want enforced on it and can try to help with the case when.
0
Get Database Help Now w/ Support & Database Audit

Keeping your database environment tuned, optimized and high-performance is key to achieving business goals. If your database goes down, so does your business. Percona experts have a long history of helping enterprises ensure their databases are running smoothly.

 
LVL 1

Author Comment

by:m_mlynek
ID: 22821860
Thanks for you help. As i have read i dont think its posible what im trying to do. My data can be too dynamic to find a pathern. Im just going to rename all data like P1-1 to P1-01 then i get the right sorting. Im giving you the points. Again thanks.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22821886
Why give up so soon.  I had a thought in mind, just wanted to make sure has at least one constant like "-" in it.  If not still can be done.  Please request the question be reopened and then you can accept correct answer.
0
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 22822580
Take a look here (the solution is at bottom of code snippet, the rest is what I used to test but figured it would be helpful to see the data I tested with so you can know what additional modifications you need for other variations of data you have if not all values have hyphen):

Can use a really large number if want numbers first then text, but easiest to go other way as don't have to guess at what largest number value could be.
CREATE TABLE `sandbox`.`testarea` (
  `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `area_nr` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`)
)
 
INSERT INTO testarea(area_nr)
SELECT '1-America'
UNION ALL
SELECT 'America-10'
UNION ALL
SELECT 'America-5'
UNION ALL
SELECT 'P1-1'
UNION ALL
SELECT 'P1-1'
UNION ALL
SELECT 'P1-1'
UNION ALL
SELECT 'P1-2'
UNION ALL
SELECT 'P1-11'
UNION ALL
SELECT 'P1-7';
 
-- sorts everything as text
SELECT area_nr, id
FROM testarea t
ORDER BY area_nr, id;
 
-- sorts text and numbers separately for each part separated by "-"
SELECT area_nr, id
FROM testarea t
ORDER BY CASE LEFT(area_nr, INSTR(area_nr, '-')-1) REGEXP '^[0-9]+$' WHEN 0 THEN 0 ELSE CAST(LEFT(area_nr, INSTR(area_nr, '-')-1) AS SIGNED) END
, LEFT(area_nr, INSTR(area_nr, '-')-1)
, CASE RIGHT(area_nr, INSTR(REVERSE(area_nr), '-')-1) REGEXP '^[0-9]+$' WHEN 0 THEN 0 ELSE CAST(RIGHT(area_nr, INSTR(REVERSE(area_nr), '-')-1) AS SIGNED) END
, area_nr, id;

Open in new window

0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question