Solved

Help With SQL; order string data like numeric

Posted on 2015-01-22
6
108 Views
Last Modified: 2015-01-22
I have a table of ages, infant, 1, 2... to 21.  The data is text, because an age can be "infant".  However, I would like SQL to sort the records with any instance of "infant" being first, and then as if the data was numeric.

Can this be done?
0
Comment
Question by:HLRosenberger
6 Comments
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 500 total points
ID: 40565036
Order by Case myage
When "infant" then -1
Else myage END;
0
 
LVL 10

Expert Comment

by:Ray
ID: 40565061
The only problem with Phillips suggestion is that if it continues to treat age as a string instead of a number it will not order correctly.  
you'd order like  
1,11,12,13...,2, 20, 21, etc.....


If you cast your values as an int along with Phillip's idea, then you get a 100% certain method.

--Something like this (did not check my syntax)
Order by    cast ( (Case when age = 'Infant' then '0' else age end)  as int)
--IFyou allow for a zero in age already, then you'd want infant to = -1 )
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40565090
Phillip's answer is correct, but just in case it isn't obvious, if the age is being stored as a varchar then you're never going to be able to perform math calculations without eliminating 'infant' as a value (which will likely have meaning), and then always converting to a numeric value.

Recommend the column be an integer data type, store infant as 0, and you can always display 'infant' in any front-end applications if you wish.
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 Closing Comment

by:HLRosenberger
ID: 40565120
Thanks!
0
 
LVL 10

Expert Comment

by:Ray
ID: 40565137
Jim,
The age is stored as text.  Knowing that, can you enlighten me (no sarcasm here), so I can have the knowledge on why/how Phillips answer will sort text strings like numbers?
IE, what condition exists here that makes this conversion 'natural'?
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40565152
Because it places the string 'infant' as a negative number, which will appears first in a numeric sort order with all other values.  (assuming it's the only other non-numeric value)

>IE, what condition exists here that makes this conversion 'natural'?
Probably implicit conversion with the first value in the CASE statement being numeric.

Although we may have to specifically cast it as a number, such as how Ray has it.  

Either way, storing age as a varchar has multiple problems.

@HLR - You'll have to test to be sure.

Question for you:  Would there ever be a valid scenario where age 3 - age 'infant' needs to be calculated, and if so how can that be performed if the data is stored as a varchar?
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql help 8 58
SQL Error - Query 6 42
SQL trigger 5 23
Hardware Resources for a Microsoft database only workstation versus .NET development only.. 6 45
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

821 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