Solved

SQL ORDER BY CASE problem

Posted on 2008-10-28
7
387 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 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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‚Ķ
This article discusses how to create an extensible mechanism for linked drop downs.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

740 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