Btukfyl

Why can't you just order it by the individual columns?

SELECT 

    date_of_entry, flag1, flag2, salary, flag3 

    , name

        + '.' + CONVERT(varchar(8), date_of_entry, 112) 

        + '.' + CONVERT(varchar(1), flag1)

        + '.' + CONVERT(varchar(1), flag2)

        + '.' + CONVERT(varchar(2555), salary)

        + '.' + CONVERT(varchar(1), flag3)

        + '.' + CONVERT(varchar(1), id) AS SYNTHETIC_ORDER

FROM  TEST

ORDER BY date_of_entry DESC, flag1 DESC, flag2 DESC, salary DESC, flag3 DESC

This will get you the MAX.

SELECT SYNTHETIC_ORDER

FROM (

    SELECT 

        ROW_NUMBER() OVER(ORDER BY date_of_entry DESC, flag1 DESC, flag2 DESC, salary DESC, flag3 DESC) AS RowNum

        , name

            + '.' + CONVERT(varchar(8), date_of_entry, 112) 

            + '.' + CONVERT(varchar(1), flag1)

            + '.' + CONVERT(varchar(1), flag2)

            + '.' + CONVERT(varchar(2555), salary)

            + '.' + CONVERT(varchar(1), flag3)

            + '.' + CONVERT(varchar(1), id) AS SYNTHETIC_ORDER

    FROM  TEST

) a

WHERE RowNum = 1

This will get you what you want but maybe not in a way you like fun to the sub-query

SELECT 

    name

    + '.' + CONVERT(varchar(8), date_of_entry, 112) 

    + '.' + CONVERT(varchar(1), flag1)

    + '.' + CONVERT(varchar(1), flag2)

    + '.' + CONVERT(varchar(2555), salary / (SELECT MIN(salary) AS min_sal FROM TEST))

    + '.' + CONVERT(varchar(1), flag3)

    + '.' + CONVERT(varchar(1), id) AS SYNTHETIC_ORDER

FROM TEST 

ORDER BY SYNTHETIC_ORDER DESC

Can you try:

SELECT name

+ '.' + CONVERT(varchar(8), date_of_entry, 112) 

+ '.' + CONVERT(varchar(1), flag1)

+ '.' + CONVERT(varchar(1), flag2)

+ '.' + CHAR(DIGITS(salary))

+ '.' + CONVERT(varchar(1), flag3)

+ '.' + CONVERT(varchar(1), id) AS SYNTHETIC_ORDER

FROM TEST

ORDER BY SYNTHETIC_ORDER DESC

If you need the "max" synthetic order, simply do:

select top (1) name, date_of_entry, falg1, flag2, salary, flag3, id

from test

order by name desc, date_of_entry desc, flag1 desc, flag2 desc, salary desc, flag3 desc, id desc;

I don't see a reason to stuff these values into a string.

If you want a separate row for each name, then:

select top (1) with ties name, date_of_entry, falg1, flag2, salary, flag3, id

from test

order by row_number() over (partition by name desc order by date_of_entry desc, flag1 desc, flag2 desc, salary desc, flag3 desc, id desc);

Sir,

As soon as you convert it to a huge string(varchar), it follows alphabetical order instead of order of magnitude like you are expecting.

Can't you just use a row_number as your "Synthetic order". In other words, instead of this:

SELECT 

name

+ '.' + CONVERT(varchar(8), date_of_entry, 112) 

+ '.' + CONVERT(varchar(1), flag1)

+ '.' + CONVERT(varchar(1), flag2)

+ '.' + CONVERT(varchar(2555), salary)

+ '.' + CONVERT(varchar(1), flag3)

+ '.' + CONVERT(varchar(1), id) AS SYNTHETIC_ORDER

FROM 

   TEST

ORDER BY 

   SYNTHETIC_ORDER DESC

this:

SELECT 

    id,

    row_number() over (order by date_of_entry,flag1,flag2,salary,flag3,id) as SYNTHETIC_ORDER

FROM 

   TEST

ORDER BY 

   SYNTHETIC_ORDER DESC

Good luck!

You can change your query to

SELECT 

name

+ '.' + CONVERT(varchar(8), date_of_entry, 112) 

+ '.' + CONVERT(varchar(1), flag1)

+ '.' + CONVERT(varchar(1), flag2)

+ '.' + CONVERT(varchar(2555), salary)

+ '.' + CONVERT(varchar(1), flag3)

+ '.' + CONVERT(varchar(1), id) AS SYNTHETIC_ORDER

FROM 

    TEST

ORDER BY 

    salary DESC

Try using the following routine.

In your code, you write CONVERT(varchar(2555), salary). This doesn't work because when you convert a float to a string using convert, the sort order of the result is not the same as the sort order of the float. e.g. 3 < 20, but '20' < '3'.

The routine FloatToSortable solves that problem. If you pass a bunch of floats through the routine, and sort on the results, you'll get the same order as if you sorted the floats. e.g. FloatToSortable(3) < FloatToSortable(20).

And so in your code, where you write

CONVERT(varchar(2555), salary)

replace it with

dbo.FloatToSortable(salary).

You say that you can't add a function to your database. That's unfortunate. I've just used functions here to avoid repetition. You can certainly use the same premise to create a single expression that will give the same result, although that expression will be much longer and harder to understand.

-- FloatToSortable takes a FLOAT parameter and returns a string

-- such that the sort order of FLOATs X and Y will match the

-- sort order of strings F(X) and F(Y).

--

-- The internal format of FLOAT is an 8-byte double-precision

-- float, starting with the SIGN where 0=positive and 1=negative,

-- followed by the EXPONENT and then the MANTISSA. 

-- If it weren't for the SIGN we could just sort by the binary

-- value.  Because of the sign we need to XOR the binary

-- before we can sort on it.  

--

-- If the parameter is positive, XOR with 8000000000000000

-- If the parameter is negative, XOR with FFFFFFFFFFFFFFFF

--

-- Then we convert each byte to a Sortable string. We could

-- use hexidecimal, but it's simpler just use letters A..O

--

-- This function is working with salaries, so we don't have

-- to worry about NANs and Infinities, but it should work

-- with all values.



-- NybbleToSortable

-- Given an integer in range 0..15 return a character

-- We just map the number to a letter, 0 -> 'A', 15 -> 'O'



create function NybbleToSortable ( @a tinyint )

returns varchar(16)

as

begin

  return char(@a + ascii('A'))

end



go



-- XorToSortable

-- Take the nth byte of @a, XOR it with the nth byte of @b,

-- and convert that byte to a Sortable string.



create function dbo.XorToSortable ( @a varbinary(8), 

                                    @b varbinary(8), 

                                    @n int )

returns varchar(16)

as

begin

  declare @aa tinyint, @bb tinyint, @x tinyint

  set @aa = cast ( substring ( @a, @n, 1 ) as tinyint )

  set @bb = cast ( substring ( @b, @n, 1 ) as tinyint )

  set @x = @aa ^ @bb

  return   dbo.NybbleToSortable ( @x / 16 ) 

         + dbo.NybbleToSortable ( @x % 16 )

end



go



create function dbo.FloatToSortable ( @x float )

returns varchar(16)

as

begin

  declare @m varbinary(8), @b varbinary(8)



  set @b = cast(@x as varbinary(8))



  if @x < 0 

    set @m = 0xFFFFFFFFFFFFFFFF

  else

    set @m = 0x8000000000000000



  return   dbo.XorToSortable ( @b, @m, 1 )

         + dbo.XorToSortable ( @b, @m, 2 )

         + dbo.XorToSortable ( @b, @m, 3 )

         + dbo.XorToSortable ( @b, @m, 4 )

         + dbo.XorToSortable ( @b, @m, 5 )

         + dbo.XorToSortable ( @b, @m, 6 )

         + dbo.XorToSortable ( @b, @m, 7 )

         + dbo.XorToSortable ( @b, @m, 8 ) 

end



go



-- Create some test data



create  table dbo.sal ( salary float, salbin as dbo.FloatToSortable(salary)) ;

go



declare @x float

set @x = pi()/9876543



while abs(@x) < 170

  begin

    insert into sal ( salary ) values ( @x )

    set @x=@x * -2.014159265

  end



select * from sal order by salbin



-- result is:

--  salary                 salbin

--  ---------------------- ----------------

--  -51.6508818660658      DPLGCMKPOHCLNIAP

--  -12.7318092715982      DPNGIJFAELIPCGOM

--  -3.1383581745746       DPPGOEKEHICIIKOI

--  -0.773597202236665     EABHDOLBBEIDLJLO

--  -0.190689716730473     EADHJHHKLHHKMEDG

--  -0.0470045237516562    EAFHOPAFOHHBPGCJ

--  -0.0115864939704268    EAHIEFFHBKJCNPMF

--  -0.00285604090440349   EAJIJKHCLHAGILBG

--  -0.000704006722693307  EALIOOFNBDCOOAMG

--  -0.000173535842863177  EANJEBBKHFNPDPAD

--  -4.27761380502506E-05  EAPJJCKPBGJEEFHA

--  -1.0544207791913E-05   EBBJODBPBNKKNIPE

--  -2.59912004745334E-06  EBDKDCGOKEJGGCDL

--  -6.4067639356036E-07   EBFKIAKBLGBGEJKE

--  3.180862629353E-07     LOJFFIKOCNMOIIKB

--  1.29042429395639E-06   LOLFKGFEIEBGJGMI

--  5.23504172442538E-06   LONFPFBFEPNNJAIF

--  2.12377138161667E-05   LOPGEEPEJEJMLAHP

--  8.61579547748313E-05   LPBGJFPGGEGGLLMK

--  0.000349528825712453   LPDGOIBOOABNBNJK

--  0.00141798166313501    LPFHDLHCDHKFMEBP

--  0.00575252124882327    LPHHIPPEKKCBMBFH

--  0.0233370441794017     LPJHOFKKIGCELCJB

--  0.094674597011311      LPLIDMJICJOMPBIA

--  0.384079459692908      LPNIJEMCADJMJBKO

--  1.55814797226306       LPPIOOCMJBHDCNED

--  6.32115319420792       MABJEINMGCAIIEAO

--  25.6438916046025       MADJKENGBEIHOPME

--  104.033102255957       MAFKACBOFIOMLAIO