Solved

SQL ORDER BY CASE problem

Posted on 2008-10-28
7
374 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Sending an image to a php webservice via cURL from command line 7 33
convert in derived column 7 30
PHP Form Calculate Total Price 10 42
SQL Error - Query 6 25
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This article discusses four methods for overlaying images in a container on a web page
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

770 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