One way 2 order results in Sql

February 16, 2006 3:45 PM

How do I order the results of sql query numerically and alphabetically?

I have a column in a products table for "Size". Typical entries are:
100 Caps
250 Caps
1000 Tablets

or any other permutation (1 oz, 3 pounds, 42 whatsits, etc)

The problem is, since this field is varchar, it accepts numbers and letters, and then orders the results in the typical way. So the list above would come out as:

100 Caps
1000 Caps
250 Caps

Which is a minor detail, but kind of annoying. Is there some way to tell the query to order this info as if it was numbers first, and then alpha?

posted by muddylemon to computers & internet (25 comments total)

You made a mistake in your table design. You need to fix it, because it'll keep biting you in the ass.

For your immediate gratification, you need to:
1. use a function that finds the index of the first space character in the column;
2. make a substring from the first character to the space character index minus one;
3. convert that substring to a number;
4. make a substring of the remainder of the column;
5. sort on the substring in step four, then the substring in step 2.

Different SQL dialects have different names for the string functions and convert function you will need to use. as I do not know what dialect you are using, I can't write the actual SQL (and you should exercise your brain by doing it anyway, now that I've supplied the algorithm).

Note that once you've got the two substrings, you can also insert them in a better designed set of tables. I'd suggest a "quantity" column and a foreign key into a product table.
posted by orthogonality at 3:55 PM on February 16, 2006

orthogonality's right. Redesign your tables if you can: change the "size" field to be absolutely numeric, and hook in the product type elsewhere.

Otherwise, both values will be essentially useless for anything other than simple display.
posted by DrJohnEvans at 4:02 PM on February 16, 2006

Probably the single easiest way, which will probably also help you elsewhere in your application, would be to separate that field into two: Quantity (a number) and Measure (capsules, tablets, whatever). This would make ORDER BY work for you, and would let you easily sort by capsules, tablets, and so on as well. Your one piece of data in that field is really two distinct things. On preview: pretty much what they said.

Aside: depending on your database, you may be able to do something like "ORDER BY to_number(Size)", which casts your field to a number on the fly (basically wiping out the text part of the field). This is really the wrong way to do it, though.
posted by jellicle at 4:07 PM on February 16, 2006

I don't think we'll redesign the database for this minor cosmetic detail - any given product tends not to have more than three or four sizes.

I think what jellicle said would work in this manner:

Select to_number(size) as numSize, size as alphaSize from products order by numSize

and then just display the alphaSize...

thanks, jellicle... didn't know about that function - is it standard sql? We use MS SQL Server.
posted by muddylemon at 4:12 PM on February 16, 2006

hmm... missed a detail of your answer jellicle - about just to_number-ing it in the order by part.
posted by muddylemon at 4:13 PM on February 16, 2006

muddylemon writes "I don't think we'll redesign the database for this minor cosmetic detail"

Trust me on this. This isn't minor and it's indicative of other problems.

For instance, your database is not normalized, which means that MOST aggregate functions won't work correctly in your database. Nor will updates. So when you decide you want to know how many bottles of V1agra you sold last month, the database won't be able to tell you -- if you're lucky. If you're unlucky, it'll tell you the wrong answer with no indication it's wrong.

Why should you believe me? I design databases for a living. What you're doing is pretty much the same as saying "I'm going to build a house, but I won't hire an architect or experienced craftsmen and I won't worry about conforming to city code." This will work basically until the first stiff breeze comes along.
posted by orthogonality at 4:24 PM on February 16, 2006

SELECT [Size],
CONVERT(INTEGER,LTRIM(RTRIM(SUBSTRING([Size],1,CHARINDEX(' ',LTRIM(RTRIM([Size]))))))) AS numSize,
LTRIM(RTRIM(SUBSTRING([Size],CHARINDEX(' ',LTRIM(RTRIM([Size]))),LEN([Size])))) AS alphaSize
FROM Test
ORDER BY numSize,alphaSize

This will work, although everyone is right, this is NOT a cosmetic detail, just a very poorly designed and normalized table.
posted by patrickje at 4:33 PM on February 16, 2006

patrickje writes "LTRIM(RTRIM("

patrickje's trimming is a good catch, which I didn't list in my initial algorithm. Otherwise, that's exactly what I specified except I think the asker wanted the order by to group the alpha, then the sizes:

10 Capsules
20 Capsules
10 Tablets
not
10 Capsules
10 Tablets
20 Capsules
posted by orthogonality at 4:40 PM on February 16, 2006

Just a note, I don't think this is the case for Muddylemon, but these issues come up ALL the time during consulting. "Just make it work, oh and it can't break any of the 567 Access Reports that Betsy in accounting wrote". So it is possible the table structure is outside of his control.
posted by patrickje at 4:41 PM on February 16, 2006

And if we're getting picky :) Then final
LEN([Size])
should really be
LEN([Size])-CHARINDEX(' ',LTRIM(RTRIM([Size]))),LEN([Size]))+1
But I'm just lazy and SQL defaults to capturing all if length specified exceeds length available.
posted by patrickje at 4:44 PM on February 16, 2006

patrickje writes "these issues come up ALL the time during consulting. 'Just make it work, oh and it can't break any of the 567 Access Reports that Betsy in accounting wrote"

With a database, the answer to this is, replace the existing tables (that "can't change" and are generally denormalized abortions) with views, and then normalize the real underlying tables. Since the view gets the old table name, it all gives you a chance to clean up the naming conventions, which are also probably fucked.
posted by orthogonality at 4:47 PM on February 16, 2006

Your database isn't normalised, but if you don't want to completely redesign your site, there are options.

Option 1, store the text prepended by spaces. Your table would look like ... (substituting dots for spaces)
...10 caps
..100 caps
.1000 caps.

This will sort correctly, and it's relatively trivial to fix data thats already there and update the inserts to append the correct number of spaces. Bonus Side Effect: extra spaces at the beginning of the data will not be shown when displaying as html. Note: Could all real programmers be aware that I know this is a stupid fix.

Option 2, add ortho's tables, and add code (in the insert again) to populate these tables when saving a products record. You can then order on the new table, but show the data from the old table.

I agree with what everyone says about normalising. Your database is wrong, and it will bite you on the ass sooner or later.
posted by seanyboy at 4:48 PM on February 16, 2006

seanyboy writes "This will sort correctly, and it's relatively trivial to fix data that's already there and update the inserts to append the correct number of spaces. Bonus Side Effect: extra spaces at the beginning of the data will not be shown when displaying as html."

Clever. It's relatively trivial, but to do it as a single update statement in one pass, he needs a SQL dialect that at least supports a log10 function and a stuff function (which he has in MS-SQL). And in Sybase he'd need to do an explicit convert from float to int for stuff's parameters. If he couldn't do the sorting without help, how's he going to do the update? (Will MS do this implicitly?)
posted by orthogonality at 4:57 PM on February 16, 2006

I agree orthogonality, but I think what's got everyone's goat is Muddylemon's flippant attitude towards normalization.

These issues are troubling and are usually signs of bad design and poor planning. But maybe they're out of scope, maybe there's a DBA on this whose pretty touchy about his 'structure'. Maybe you're just in charge of making the website, and only have rights to write stored procedures. Maybe the database structure is federated to other applications and table changes are verboten.

The question asked was basically how to sort a text field numerically and alphabetically. I agree refactoring is the solution most of us would choose. Sometimes we don't have that choice.
posted by patrickje at 4:58 PM on February 16, 2006

patrickje writes "Sometimes we don't have that choice."

Agreed, agreed! (Although we do, if consultants, have a fiduciary responsibility to document the inevitable costs of doing it the wrong way). But I think it's clear (and it gets my goat too) that Muddylemon has the choice, and doesn't understand why it's important.
posted by orthogonality at 5:06 PM on February 16, 2006

orthogonality writes:Muddylemon has the choice, and doesn't understand why it's important.

Thanks for making your condecension so clear.

Now, I'll explain why it's not important:

1. The data comes into our database from really crappy vendor sources, so parsing out their million and a half ways of describing their products is not a priority.


2. The only time this issue affects anything, is when our products are displayed in a list to our customers - and really, it's a minor detail. There are maybe three or four sizes to choose from, the fact that 250 caps is displayed after 1000 caps will not kill any starving puppies.


3. Reports aren't generated by the size field, or the name of the product. That's why God made ID numbers, UPC codes and SKUs

For instance, your database is not normalized, which means that MOST aggregate functions won't work correctly in your database. Nor will updates. So when you decide you want to know how many bottles of V1agra you sold last month, the database won't be able to tell you -- if you're lucky. If you're unlucky, it'll tell you the wrong answer with no indication it's wrong.
Why should you believe me? I design databases for a living. What you're doing is pretty much the same as saying "I'm going to build a house, but I won't hire an architect or experienced craftsmen and I won't worry about conforming to city code." This will work basically until the first stiff breeze comes along.

This is just stupid. "Size" is a customer facing descriptive field. You know what? My client likes to throw font tags into the product names - it annoys me, but I can live with it. Why? because we have things called primary keys. That little id field has a hell of a lot more to do with generating our reports than the descriptive fluff fields. We also have ingredients for these products. I suppose I could spend thousands of dollars of my clients money creating a system that would normalize all that data into discrete little chunks. Hell, then we could make a search app so our users could find all products with 300 milligrams of Acetyl L-Carnitine AND 100 mg of rice flour. But that's useless. The ingredients field is just descriptive information and sits happily in a little chunk of text.
I was asking if there's a neat little hack that would display things a little better, not if beard stroking dba's would take time from their schedules to tell me I don't know how to do my job.
And for the record, I do understand and believe in normalization. But not to the point that we'd have to hire a gaggle of data entry typers to spend all day parsing our vendors arbitrary conventions. The site has 32k products in 300+ brands in 750+ categories with a couple million customers, several hundred orders a day and a couple million in sales a month. And it works just dandy, thank you.
posted by muddylemon at 6:48 PM on February 16, 2006

Maybe you should have stopped at "Thanks".
posted by sad_otter at 8:24 PM on February 16, 2006

The solution that Patrickje will do exactly what you want, but you need to be aware that the performance could be fairly lousy on large data sets. You may run into performance problems, at which point you'll have to look at redesigns. I don't know what sort of database you're using, but if it's SQL server, you'll probably want to create a view with the calculate fields, if it's Access, you can create a query, otherwise... good luck!
posted by blue_beetle at 9:06 PM on February 16, 2006

muddy, getting all haughty and spitting at the real DBA, when you had to ask for help on how to sort a text field, is not the most intelligent behavior I've seen.

I think ortho made the mistake of thinking that someone with that level of question must be just getting started in database design. He was trying to help you avoid a mistake.

The fact that you're pumping a pretty good amount of traffic through a database, btw, is not at all an argument that it's well designed. The fact that you're putting enough money through it to afford a real DBA, but you're still asking this question, strongly suggests quite the opposite.

Pay attention to what ortho is saying. Slamming him as a 'beard stroking DBA', when you're asking such a trivial question, speaks very poorly of you.
posted by Malor at 11:27 PM on February 16, 2006

I was asking if there's a neat little hack that would display things a little better, not if beard stroking dba's would take time from their schedules to tell me I don't know how to do my job.

I was about to put in my $.02 to help until I just now learned you were an ass.
posted by glenwood at 5:40 AM on February 17, 2006

posted by muddylemon: "Size" is a customer facing descriptive field.
You might not have gotten your feelings hurt if you'd mentioned that up front.
posted by boo_radley at 5:47 AM on February 17, 2006

What Glenwood said
posted by hmca at 5:54 AM on February 17, 2006

Well I'm sorry for being an ass - but it pissed me off that the only answer was "you don't know what your doing." I've been developing databases and projects like this for over 10 years, so the assumption that this is some hacked together access database and I'm an amateur does make me mad.
posted by muddylemon at 8:06 AM on February 17, 2006

muddylemon writes "it pissed me off that the only answer was 'you don't know what you[']r[e] doing.'... the assumption that this is some hacked together access database and I'm an amateur does make me mad."

You'll note that my answer was posted at 4:55 PM EST, exactly ten minutes after you posted the question. Jellicle gave an answer twelve minutes after that, and twenty-five minutes after that patrickje gave you the actual SQL statement, in your SQL's dialect.

This is pretty convincing evidence that the answer was not a difficult one.

All three of these answers, and in addition two others (by DrJohnEvens and seanyboy) pointed out that your table structure was probably incorrect.

Given that you couldn't readily come up with, and had to ask for help with, a relatively trivial question (a question simpler than many I'd use in an interview for a SQL programmer or DBA), and given that you offered no evidence to the contrary, it seemed a reasonable assumption that your were in fact an amateur who didn't know what he was doing.

As it turns out you're a professional with ten years of experience (since you're twenty-eight, I suppose that means the experience doesn't include a four year college degree, but formal edition is just a scam for pointy-headed inta-lec-shuls who stroke their DBA beards, we all know that), and as your database produces millions of operations and millions of dollars every month, which is the only possible criterion of correctness, just ask Esdel Ford, I humbly apologize for stroking my DBA beard and having the temerity to try to help you, when despite your years of professional experience, your professional designed database didn't work well enough to give you the output you needed.

Please forgive me sir for assuming that since what you had didn't work and you couldn't figure out how to make it work yourself, I was so brash as to assume that there might exist things you didn't know, and take the time to -- with no profit to myself -- try to help you make your database work the way you wanted it to work but couldn't achieve on your own. I do apologize! You are truly much more of an experienced professional than any "beard stroking dba's".

(And I will not point out that in "beard stroking dba's", the apostrophe indicates possession, not the plural, because after all, you are an experienced professional and no one reading what you write could ever possibly gather any other impression.)
posted by orthogonality at 9:11 AM on February 17, 2006

Well, we're all dicks, I guess.
posted by boo_radley at 10:26 PM on February 17, 2006