How to Find MAX value from the text field

Question

question

sadi asked Apr 13, '10

How to Find MAX value from the text field

Hi-

I have a CYC_CD field which is VARCHAR. The values of this field indicate the cycle run code For Example 0109M(January 09 run), 1209M (December 09 run) so on so forth. When I convert it to an integer and use MAX function it gives me incorrect result becauase technically 0310M is the Max run and not 0409M .

Is there a way where I can pick up the MAX value in the text field. In my case pick up 0310M record and not 0409M record ?

Thanks SM

t-sql cast-convert

2

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Jeff Moden commented · Apr 13, 2010 at 10:09 PM

Before I give an answer on this, what does the "M" stand for in each of these dates?

0 ·

Jeff Moden commented · Apr 19, 2010 at 02:05 AM

I'm still waiting for an answer on this. It could be real easy if I knew what the bloody "M" stood for. ;-)

0 ·

Answer 1 · 2010-04-13T04:36:44Z

Fatherjack answered Apr 13, '10

If this was something that would be used frequently then I think that I would build a lookup table purely made up of your cycle code in one column and an integer in another. This table would maintain your sort order (you would have to insert the cycle codes in the right order etc) and you could simply join to this table when you want your results sorted. this would be more efficient for your database than always parsing the cycle code to split the month and year out and then calculating the order.

2

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

TimothyAWiseman commented · Apr 13, 2010 at 10:18 AM

Good advice. Similarly if you can make a deterministic user defined function you can add that as a computed column and index it. Without thorough testing, I think the computed column would probably be faster on the read side, but you would pay for it on the write side as it had to computer and store the new value then. Of course, that depends on being able to write that function in a way it considers deterministic, which should be easy but I have not tested this scenario.

1 ·

Matt Whitfield ♦♦ commented · Apr 13, 2010 at 10:54 AM

+1 - certainly, and +1 @Tim's comment

0 ·

Answer 2 · 2010-04-13T02:30:04Z

Daniel Ross answered Apr 13, '10

This might get what you need, you will have to move the year value to the front, and then the month value,

select top 1 max(substring(cyc_cd,3,2) + substring(cyc_cd,1,2)) ,cyc_cd

from testtest group by cyc_cd order by 1 desc

2

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Daniel Ross commented · Apr 13, 2010 at 02:31 AM

Or do it like this; select top 1 * from testtest order by substring(cyc_cd,3,2) + substring(cyc_cd,1,2) desc

2 ·

Matt Whitfield ♦♦ commented · Apr 13, 2010 at 03:21 AM

+1 - the way in your comment is definitely better in terms of what the user wants to get back, although neither are very efficient (pretty much constrained by the ddl)

0 ·

question