MYSQL Next Value numeric/alpha table entries

[dlebowski]

Below is the query I have an it works good if I want the next lot in the table sorted by "asc" and there are no alpha characters involved.  The issue I run into is sometimes the LotNumber may have alpha characters in it.  For example, 1a, 1b, 2, 2d, 2dd, etc.

 

So if my order is 1a, 1b, 2, 2d, 2dd, 3

and $lot = 2,

the next value I want from the query below is 2d. 

 

Unfortunately, I get 3 for the next value.  Can someone show me what I'm doing wrong?  Thank you.

SELECT LotNumber, LotTitle, LotDescription, LotImage1, LotImage2, LotImage3, LotImage4, LotImage5, LotImage6 FROM `lots` WHERE LotDate='$date' and convert(LotNumber, signed)>'$lot' GROUP BY convert(LotNumber, signed) ORDER BY CAST(LotNumber AS UNSIGNED) asc LIMIT 1

[Mchl]

Change

ORDER BY CAST(LotNumber AS UNSIGNED)

to

ORDER BY LotNumber

[dlebowski]

Hi  Thanks for the fast reply.  The issue is that the field if varchar and it will sort like this 1, 10, 100, 2, 20, 200, etc..

 

I need it to sort like this 1, 2, 10, 20, 100, 200.

[Mchl]

Hmpfff... Maybe like this then

 

ORDER BY CAST(LotNumber AS UNSIGNED), lotNumber

[dlebowski]

It still only displays the numeric characters.

 

ORDER BY CAST(LotNumber AS UNSIGNED), LotNumber asc 

[Mchl]

Yeah... that's because you have this part

GROUP BY convert(LotNumber, signed) 

which seems totally unnecessary, since you use no aggregating functions.

[dlebowski]

I removed that and now.  If i have 1, 1a, 2, 2a and $lot=1, then it gives me 2, 2a.  It skips 1a which is the next value. 

 

convert(LotNumber, signed)>'1'  ORDER BY CAST(LotNumber AS UNSIGNED), LotNumber asc 

[Mchl]

How about this?

convert(LotNumber, signed)>='1'  ORDER BY CAST(LotNumber AS UNSIGNED), LotNumber asc LIMIT 1,1

[dlebowski]

That won' work either because if you have 1,2,3,4,5 (no alpha characters) and $lot=1, it will give you 1 for a value instead of 2. 

[Mchl]

Did you check? It takes second row from resultset, so it should return 2

[dlebowski]

Yes, I checked.  Here is what I have right now and if I have 1, 1a, 2, 3, 3a and $lot=1, I get 2.  If $lot=0, then I get 1a.  If $lot=1a, then I get a value of 3.

 

SELECT LotNumber, LotTitle, LotDescription, LotImage1, LotImage2, LotImage3, LotImage4, LotImage5, LotImage6 FROM `lots` WHERE LotDate='2010-07-15' and convert(LotNumber, signed)>'$lot' ORDER BY CAST(LotNumber AS UNSIGNED), LotNumber asc LIMIT 1,1

 

If I do this, it will give me the correct value ONLY if there is not an alpha character involved.  If I have 1, 1a, 2, 3, 3a and $lot=1, I get 2.  If $lot=0, then I get 1.  If $lot=1a, then I get a value of 2.

 

SELECT LotNumber, LotTitle, LotDescription, LotImage1, LotImage2, LotImage3, LotImage4, LotImage5, LotImage6 FROM `lots` WHERE LotDate='2010-07-15' and convert(LotNumber, signed)>'$lot' ORDER BY CAST(LotNumber AS UNSIGNED), LotNumber asc 

[Mchl]

I'd like to point out that in my query there is

convert(LotNumber, signed)>='$lot'

 

not

convert(LotNumber, signed)>'$lot'

[dlebowski]

You are right.  I did change that.  Now here is what happens.  If I have 1, 1a, 2 and $lot=1a, I get 1a returned as a value which is incorrect.  If I have $lot=1, i get a returned value of 1a which is correct.  Thank you very much for trying to help me with this.

 

SELECT LotNumber, LotTitle, LotDescription, LotImage1, LotImage2, LotImage3, LotImage4, LotImage5, LotImage6 FROM `lots` WHERE LotDate='2010-07-15' and convert(LotNumber, signed)>='$lot' ORDER BY CAST(LotNumber AS UNSIGNED), LotNumber asc LIMIT 1,1

[Mchl]

I'm starting to thinking there's no reasonable solution to this :/

[dlebowski]

Well, it definitely wasn't for a lack of effort!  Now I just got to try and figure out how to explain this to my customer.    If you can think of anything else, let me know.  I will post any solution that I may come across.  Thanks again for all your help.

[Mchl]

One thing that comes to my mind is this:

 

maybe there's some kind of DATE field associated with lot number that we could use?

[dlebowski]

Are you asking if the date/time the value is entered is a factor in determining the order?

[Mchl]

Yes. Or is it that for example all lots starting with 1 can be ordered by date, and then lots starting with 2 and so on.

[dlebowski]

Well, I don't think that will work for me either because it is strictly sorting by that value.  There really isn't any other factors involved.  If I have

 

LotNumber

1

2

2a

2b

3

4

 

I want "LotNumber>2a Limit 1" to be 2b.

 

[Mchl]

What if you could split LotNumber into two columns.

 

LotNumeric, LotAlphabetic

1, null

2, null

2, a

2, b

3, null

4, null

 

(make sure numeric part is stored as numeric datatype)

 

Then sorting on these two columns

 

ORDER BY LotNumeric, LotAlphabetic

 

should yield required order.

[dlebowski]

Not a bad idea.  I would have to change a lot of stuff with my other queries that work OK, but it may work. 

 

What about this:

 

Can I use "count" somehow.  To say, count values ascending.  $lot=7 would be the 8th value in the list, give me the 9th value?  Would that work?  How could I do that?

[dlebowski]

Does this make any sense why this would work?  So far, it's working.  I'm trying to break it, but it seems to work.

 

SELECT LotNumber, LotTitle, LotDescription, LotImage1, LotImage2, LotImage3, LotImage4, LotImage5, LotImage6 FROM `lots` WHERE LotAuctionDate='2010-07-17' and convert(LotNumber, signed)>='$lot' and LotNumber>'$lot' ORDER BY CAST(LotNumber AS UNSIGNED), LotNumber asc 

 

If I have this:

 

1

1a

1b

2

3

 

if $lot=1a, the returned value will be 1b which is correct.  If $lot=2, the returned value will be 3 which is also correct.

[fenway]

Not that it would be particular efficient, but you could strip out the numeric part dynamically and then sort of the remaining string as a secondary ordering clause.