ORDER BY (numbers and letters)

[Glen T]

I've got a table of data that I want to sort by lot number. The lots are in the
form of 1A, 1B, 2A and so on. Obviously, this is a text field. So when I use
ORDER BY I get all the first digits grouped together, and so on, which I don't
want.

How does one sort a hibrid of numbers and letters so that you get the expected
results?

[Dan Bracuk]

What are the expected results?


Originally posted by: Glen T
I've got a table of data that I want to sort by lot number. The lots are in
the form of 1A, 1B, 2A and so on. Obviously, this is a text field. So when I
use ORDER BY I get all the first digits grouped together, and so on, which I
don't want.

How does one sort a hibrid of numbers and letters so that you get the expected
results?

[mxstu]

Well, if the numeric portion of the lot numbers were padded with zeroes, they
would sort correctly:

001A
002A
013A

... instead of ...

13A
1A
2A


Or if the lot number is only one level deep [number]+[letter] you could
probably separate the value into number + alpha columns and then sort by:
lotNumber, lotLetter

[Glen T]

The lot numbers are actual lot numbers, used as addresses on a strata-title
vacation property. So, I'd be hesitant to pad them with zeros.

I'd like them to come out like this:

1A
1B
2A
2B
...
9A
9B
10A
10B
11A
...
33B

Instead, what I'm getting is this:
10A
10B
...
19A
19B
1A
1B
20A
20B
...

[mxstu]

What are the rules for the lot numbers? Are they always a single number +
letter code ? Or is possible they can vary? If they will never vary from that
format, you might be able to use a database view for sorting the values.

[Glen T]

The range is pretty simple. Starts at 1A and goes in A-B pairs to a max of 33.

[mxstu]

Then just create a sql view that presents a virtual view of the table plus two
columns: the "number" and "alpha" portion of the values. Then you can sort by
those columns instead of sorting by lotNumber ("1A", etc). That will work.

[Glen T]

I found the following snippet of SQL code:

Order By Case When IsNumeric(Right(StreetAddr, 1)) = 1
Then right('00000' + StreetAddr + ' ', 5)
Else Right('00000' + StreetAddr, 5)
End

But I really don't know how to adapt this to CF. This is similar to what I
want to do:

[url]http://www.tek-tips.com/viewthread.cfm?qid=1132304&page=1[/url]

[mxstu]

I think it is even simpler in your case. If the numbers are a max of two
digits and the letter code is only one digit.

SELECT lotNumber
FROM yourTable
ORDER BY
CAST(LEFT(lotNumber, LEN(lotNumber)-1) AS int),
RIGHT(lotNumber, 1)

Although, I really recommend putting this in a "view". It will make your code
much cleaner.

[mxstu]

Hmm.. on second look at the example from the link your posted, you could
probably reduce it to this

SELECT lotNumber
FROM yourTable
ORDER BY RIGHT('00000' + lotNumber, 3)

The reason being that your "lotNumbers" always have an alpha character at the
end. The poster in the link did not.

[Glen T]

Here's the complete query, but I'm getting missing parameter error on the ORDER
BY clause:

SELECT fld_ID, fld_lotNumber, fld_lastName1, fld_firstName1, fld_lastName2,
fld_firstName2, fld_lastName3, fld_firstName3, fld_email1, fld_email2
FROM tbl_members
ORDER BY CAST(LEFT(fld_lotNumber, LEN(fld_lotNumber)-1) AS int),
RIGHT(fld_lotNumber, 1)

[mxstu]

What database are you using?

Did you see my last post? That modification of the code you found is even simpler.

[Glen T]

Yes, this works just right:

SELECT lotNumber
FROM yourTable
ORDER BY RIGHT('00000' + lotNumber, 3)

Thanks a bunch, mstu!

[Glen T]

I'm using Access, so that probably explains the missing parameter issue.

[Glen T]

This brings up an interesting point that I hadn't really considered before
(because I'm a database virgin). That is, I didn't realize that I was actually
operating Access functions via CF / SQL. I kind of assumed that an Access file
was just data structured in a certain format, and that the functions lived in
the server side of CF.

[mxstu]

Yes, it is an important distinction. Take these two queries

<!--- example 1 --->
<cfquery .....>
SELECT NOW() AS CurrentDateAndTime
</cfquery>

<!--- example 2 --->
<cfquery .....>
SELECT #NOW()# AS CurrentDateAndTime
</cfquery>

They look similar, but the "example 1" uses the Access database funtion named
"now()" and "example 2" uses the cf function named "now()". Although they both
do essentially the same thing. Kind of trippy, huh? ;-)

[Glen T]

More than trippy, it is a bit distressing. Means that this stuff is even less
portable than I thought it would be. You have juggle database-specific stuff
along with the application platform-specific stuff and the html development
platform-specific stuff. I guess you really are painting yourself into a corner
when you commit to a platform.

[mxstu]

Well.. if you're looking for portable, I wouldn't use Access. I don't know
about the newer versions, but the earlier versions did not conform to some of
the generally accepted sql standards.

With most databases, if you stick to the ansi standards your code can be
relatively portable. However, the other school of thought is that you may as
well take advantage of the software you have. Why bother sinking money into a
product if you don't use many of it's capabilities.

[Glen T]

Originally posted by: mxstu
Well.. if you're looking for portability, I wouldn't use Access. I don't know
about the newer versions, but the earlier versions did not conform to some of
the generally accepted sql standards.

With most databases, if you stick to the ansi standards your code can be
relatively portable. However, the other school of thought is that you may as
well take advantage of the software you have. Why bother sinking money into a
product if you don't use many of it's capabilities.
I agree with you to some extent. But sometimes, you don't have a choice. For
example, right now I'm doing a small job for a client whose ISP only supports
Access.

What I have to keep in mind is that I will not necessarily be able to reuse
work I've done for others without modification. But then, that's probably the
way of the world. And I agree that there is no point in failing to exploit the
full capabilities of the tools you are currently using. I just wish that I
could count on taking those tools with me from one job to the next. Wishful
thinking, of course...