Solved

SQL ORDER BY CASE problem

Posted on 2008-10-28
7
382 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
length of the password hash sha1:64000 to set sql field property. 13 66
MySQL - need to join three tables 2 46
Stored Proc - Rewrite 42 56
IIF in access query 19 23
This article discusses four methods for overlaying images in a container on a web page
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
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…

792 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