Bad ordering when ordering by varchar field

Hi guys,
did anyone come across this problem?
I have a query where I ORDER BY Part_Number field which is varchar but
the ordered result is
10169-G
10169-MVS
10169
which is mess because normal person would put first the 10169 and then
the others?
Thanks for any help Milan
hi Milan,
Milan Reznicek wrote:
> Hi guys,
> did anyone come across this problem?
> I have a query where I ORDER BY Part_Number field which is varchar
> but the ordered result is
> 10169-G
> 10169-MVS
> 10169
> which is mess because normal person would put first the 10169 and
> then the others?
> Thanks for any help Milan
can you please post your actual code, as simple repro are correct, on my
instance..
SET NOCOUNT ON
DECLARE @.t TABLE ( Part_Number varchar(10) )
INSERT INTO @.t VALUES ( '10169-G' )
INSERT INTO @.t VALUES ( '10169-MVS' )
INSERT INTO @.t VALUES ( '10169' )
SELECT t.Part_Number
FROM @.t t
ORDER BY t.Part_Number
--<--
Part_Number
10169
10169-G
10169-MVS
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||What collation is your database using?
"Milan Reznicek" <reznicek@.rezna.info> wrote in message
news:uZCQw9ZSFHA.356@.TK2MSFTNGP14.phx.gbl...
> Hi guys,
> did anyone come across this problem?
> I have a query where I ORDER BY Part_Number field which is varchar but
> the ordered result is
> 10169-G
> 10169-MVS
> 10169
> which is mess because normal person would put first the 10169 and then
> the others?
> Thanks for any help Milan
>
|||Thanks both, you and JJ. Your code is working all right until I add my
collation which is SQL_Czech_CP1250_CI_AS (JJ pointed about it) - and which
completely messes up the ordering.
Milan
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> pe v diskusnm pspvku
news:3d4df0F6opoh9U1@.individual.net...
> hi Milan,
> Milan Reznicek wrote:
> can you please post your actual code, as simple repro are correct, on my
> instance..
> SET NOCOUNT ON
> DECLARE @.t TABLE ( Part_Number varchar(10) )
> INSERT INTO @.t VALUES ( '10169-G' )
> INSERT INTO @.t VALUES ( '10169-MVS' )
> INSERT INTO @.t VALUES ( '10169' )
> SELECT t.Part_Number
> FROM @.t t
> ORDER BY t.Part_Number
> --<--
> Part_Number
> --
> 10169
> 10169-G
> 10169-MVS
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||hi Milan,
Milan Reznicek wrote:
> Thanks both, you and JJ. Your code is working all right until I add my
> collation which is SQL_Czech_CP1250_CI_AS (JJ pointed about it) - and
> which completely messes up the ordering.
of course collation change the way the sort order is performed...
you can, if you are allowed to from your app design, change the sort order
rules for queries like that like
SET NOCOUNT ON
DECLARE @.t TABLE ( Part_Number varchar(10) )
INSERT INTO @.t VALUES ( '10169-G' )
INSERT INTO @.t VALUES ( '10169-MVS' )
INSERT INTO @.t VALUES ( '10169' )
SELECT t.Part_Number
FROM @.t t
ORDER BY t.Part_Number COLLATE Latin1_General_BIN
you specify that way a different sort order and rule the order by clause
must conform with... but verify this do not comprimise other app designs and
constraints...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply