How to get required order in table box

[#1]

Hi,

I have a table box . In the edit script I have below one

Data:

LOAD

    ID,

   Reference;

SQL SELECT *

FROM "table"

where Reference in ('371284',

'373896',

'374493',

'411464',

'411464',

'413172',

'415033',

'415623',

'454035',

'442363',

'455699',

'457311',

'458105',

'326600',

'326600',

'455024',

'453906',

'1851492',

'1851492',

'1851492',

'455839',

'457788',

'330983',

'328251',

'372278',

'376328',

'369175',

'369175',

'369175',

'386311',

'426283',

'425362',

'424456',

'418055',

'426787',

'386026',

'386026',

'387355',

'386161',

'418753',

'472508',

'472508',

'1850066',

'423629',

'504005',

'382816',

'388291');

I need to have the Reference as above order in Table box.could anyone help me is that possible to create like above. I think I have to write expression in Sort tab is that correct?

Thanks.

[#2]

Please any help???

[#3]

If your database is MySQL you could use Field(), and use RowNo() in the LOAD statement as a sort key e.g.

Data:

LOAD

    RowNo() as SortOrder,

    ID,

   Reference;

SQL SELECT *

FROM "table"

where Reference in ('371284',

'373896',

'374493',

'411464',

'411464',

'413172',

'415033',

'415623',

'454035',

'442363',

'455699',

'457311',

'458105',

'326600',

'326600',

'455024',

'453906',

'1851492',

'1851492',

'1851492',

'455839',

'457788',

'330983',

'328251',

'372278',

'376328',

'369175',

'369175',

'369175',

'386311',

'426283',

'425362',

'424456',

'418055',

'426787',

'386026',

'386026',

'387355',

'386161',

'418753',

'472508',

'472508',

'1850066',

'423629',

'504005',

'382816',

'388291')

ORDER BY FIELD(Reference,

'371284',

'373896',

'374493',

'411464',

'411464',

'413172',

'415033',

'415623',

'454035',

'442363',

'455699',

'457311',

'458105',

'326600',

'326600',

'455024',

'453906',

'1851492',

'1851492',

'1851492',

'455839',

'457788',

'330983',

'328251',

'372278',

'376328',

'369175',

'369175',

'369175',

'386311',

'426283',

'425362',

'424456',

'418055',

'426787',

'386026',

'386026',

'387355',

'386161',

'418753',

'472508',

'472508',

'1850066',

'423629',

'504005',

'382816',

'388291');

[bumin]

have you tried with loadorder?

[#5]

Thanks and I have SQL Server 2008. please helpme how to change this as I am trying this in different ways...

[#6]

There is no direct equivalent in SQL Server but you could try something like this (just using a few Reference values to simplify the example):

Data:

LOAD

    RowNo() as SortOrder,

    ID,

   Reference;

SQL SELECT *

FROM "table"

where Reference in ('371284',

'373896',

'374493',

'411464')

ORDER BY CASE Reference

           WHEN '371284' THEN 1

           WHEN '373896' THEN 2

           WHEN '374493' THEN 3

           WHEN '411464' THEN 4

           ELSE 5

         END;

It's a pain to type so try it with a few values only first to see if it works. I don't have SQL Server installed so cannot test. If it works then the TableBox can be sorted by field SortOrder.

[bumin]

try this

DataLoadOrder:

LOAD * Inline [

'373896',

'374493',

'411464',

'411464',

'413172',

'415033',

...

'423629',

'504005',

'382816',

'388291'

];

DataOrder:

LOAD

     ID,

   Reference;

SQL SELECT *

FROM "table"

where Reference in ('371284',

'373896',

'374493',

'411464',

'411464',

'413172',

'415033',

...

'423629',

'504005',

'382816',

'388291');

drop table DataLoadOrder;

[#8]

Good call Bumin.

Slight tweak (add column header and remove commas):

DataLoadOrder:

LOAD * Inline [

Reference

'373896'

'374493'

'411464'

'411464'

'413172'

'415033'

...

'423629'

'504005'

'382816'

'388291'

];

DataOrder:

LOAD

     ID,

   Reference;

SQL SELECT *

FROM "table"

where Reference in ('371284',

'373896',

'374493',

'411464',

'411464',

'413172',

'415033',

...

'423629',

'504005',

'382816',

'388291');

drop table DataLoadOrder;

[bumin]

thanks