Ordering By Numeric, then by alphanumeric

[mattpont]

Hi,

I have a data table with shop products in it.

I want to order the data on the 'size' column.

This can be shoe size (9, 10, etc) or it could be shirt size (Large, Medium, etc).

When I order by alphanumeric, the shoe size list looks wrong as it shows this:

10, 10.5, 11, 11.5, 6, 7, 8, 9

But, if I order by numeric (Using CAST for example), it obviously doesn't like the alpha sizes like 'Large'.

Anyone know a way to solve this? Or am I going about it in completely the wrong way (probably!)?

Thanks for any help.

 

[Crowley16]

the proper way would be to split out your sizes into a different table with a name field for displaying and an ordering field which would be hidden to the users and ranked however you want...


--------------------
Procrastinate Now!

 

[bborissov]

DECLARE @Temp TABLE (SizeOf varchar(20))
INSERT INTO @Temp VALUES ('10')
INSERT INTO @Temp VALUES ('10.5')
INSERT INTO @Temp VALUES ('11')
INSERT INTO @Temp VALUES ('11.5')
INSERT INTO @Temp VALUES ('6')
INSERT INTO @Temp VALUES ('7')
INSERT INTO @Temp VALUES ('8')
INSERT INTO @Temp VALUES ('9')
INSERT INTO @Temp VALUES ('Large')
INSERT INTO @Temp VALUES ('Medium')

SELECT * from @Temp
ORDER BY RIGHT(REPLICATE('0',20)+SizeOf,20)

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[mattpont]

Wicked, works perfectly.

Thanks for both of your very swift reponses.

 

[ESquared]

I have a suspicion that that WON'T work. Look at your 10 and 10.5...

see wrong sort order:

000010
0010.5
000011



[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]

 

[ESquared]

DECLARE @Temp TABLE (SizeOf varchar(20))
INSERT INTO @Temp VALUES ('7')
INSERT INTO @Temp VALUES ('6')
INSERT INTO @Temp VALUES ('10')
INSERT INTO @Temp VALUES ('11')
INSERT INTO @Temp VALUES ('Large')
INSERT INTO @Temp VALUES ('10.5')
INSERT INTO @Temp VALUES ('11.5')
INSERT INTO @Temp VALUES ('8')
INSERT INTO @Temp VALUES ('9')
INSERT INTO @Temp VALUES ('Medium')

select * from @temp order by case when isnumeric(sizeof) = 1 then convert(decimal(12,2), sizeof) else null end, sizeof

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]

 

[bborissov]

Not only suspicions, you are right, DAMN! Where I was looked at?

DECLARE @Temp TABLE (SizeOf varchar(20))
INSERT INTO @Temp VALUES ('10')
INSERT INTO @Temp VALUES ('10.5')
INSERT INTO @Temp VALUES ('11')
INSERT INTO @Temp VALUES ('11.5')
INSERT INTO @Temp VALUES ('6')
INSERT INTO @Temp VALUES ('7')
INSERT INTO @Temp VALUES ('8')
INSERT INTO @Temp VALUES ('9')
INSERT INTO @Temp VALUES ('Large')
INSERT INTO @Temp VALUES ('Medium')

SELECT * from @Temp
ORDER BY RIGHT(REPLICATE('0',20)+
              CASE WHEN CHARINDEX('.',SizeOf) = 0
                   THEN SizeOf+'.00'
                   ELSE LEFT(SizeOf,CHARINDEX('.',SizeOf))+
                        LEFT(SUBSTRING(SizeOf,CHARINDEX('.',SizeOf)+1,2)+'00',2)
                   END,20)

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP