#1  
Old 23rd October 2020, 11:10 PM
David Rose
 
Posts: n/a
Default Order by my sequence


Is there any way in SQL to specify the order by sequence.
e.g a pseudo code of

Select CustomeName from Customers
Where CustomerNumber in [105, 25, 47, 88, 91, 1000]
Order By CustomerNumber sequence [105, 25, 47, 88, 91, 1000]

Which would give the result

Customer105
Customer25
Customer47
Customer88
Customer91
Customer1000

Thanks

David
  #2  
Old 24th October 2020, 12:21 AM
Ad Franse
 
Posts: n/a
Default Re: Order by my sequence

Op 23-10-2020 om 13:10 schreef David Rose:
>
> Is there any way in SQL to specify the order by sequence.
> e.g a pseudo code of
>
> Select CustomeName from Customers
> Where CustomerNumber in [105, 25, 47, 88, 91, 1000]
> Order By CustomerNumber sequence [105, 25, 47, 88, 91, 1000]
>
> Which would give the result
>
> Customer105
> Customer25
> Customer47
> Customer88
> Customer91
> Customer1000
>
> Thanks
>
> David


if there is nothing you can order by then
introduce a sortfield varchar(32)
Ad Franse

drop table if exists t1;
create table t1
(
id autoinc primary key
, customernumber integer
, customername varchar( 64)
, sortfield varchar( 32)
);

insert into t1 ( customernumber, customername, sortfield) values ( 105,
'Customer 105', 'A' );
insert into t1 ( customernumber, customername, sortfield) values ( 25,
'Customer 24', 'B' );
insert into t1 ( customernumber, customername, sortfield) values ( 47,
'Customer 47', 'C' );
insert into t1 ( customernumber, customername, sortfield) values ( 88,
'Customer 88', 'D' );
insert into t1 ( customernumber, customername, sortfield) values ( 99,
'Customer 99', 'E' );
insert into t1 ( customernumber, customername, sortfield) values (1000,
'Customer 1000', 'F' );

select *
from t1
order by sortfield;
  #3  
Old 24th October 2020, 01:15 AM
David Rose
 
Posts: n/a
Default Re: Order by my sequence

>
> if there is nothing you can order by then
> introduce a sortfield varchar(32)
> Ad Franse
>
> drop table if exists t1;
> create table t1
> (
> ¬* id autoinc primary key
> , customernumber integer
> , customername varchar( 64)
> , sortfield varchar( 32)
> );
>


Thanks. That's not practical in this scenario, but it made me realise
that I can probably create a temp table called #Sequence with two fields

CustomerID & SortOrder and then join that temp table to the original
query and Order by the SortOrder field.

Thanks

David


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Interresting Article : Sequence vs Identity Clément Doss nexusdb.public.discussions 11 21st December 2012 09:38 AM
Filtering Order (index applied order)? Shane Stump nexusdb.public.support.sql 4 3rd July 2010 03:04 PM
Sort order of strings in ORDER BY clause Stefan Paege nexusdb.public.support.sql 6 6th March 2010 03:09 AM
Sequence Checking John Turner nexusdb.public.support.sql 2 27th February 2009 06:56 AM
Avoid appear/disappear sequence of printer icon Hugo Galindo nexusdb.public.discussions 4 13th October 2007 04:07 AM


All times are GMT +11. The time now is 10:53 PM.


Powered by vBulletin® Version 3.6.8
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.