Solved

SQL ORDER BY CASE problem

Posted on 2008-10-28
7
366 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
  • 4
  • 2
7 Comments
 
LVL 59

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 59

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 59

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 59

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This article discusses how to create an extensible mechanism for linked drop downs.
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 …

920 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now