Order by

Hi,

I have an output like this:

DATA_1      Counts      CreatedDate
8004229146      2154      2/25/2014
8005437765      53      2/25/2014
8008434876      638      2/25/2014
8773542583      992      2/25/2014
8776712583      963      2/25/2014
8777902583      3025      2/25/2014
8882881718      78      2/25/2014


but what I want is to have them listed in a certain order

like this
DATA_1      Counts      CreatedDate
8882881718      78      2/25/2014
8008434876      638      2/25/2014
8005437765      53      2/25/2014
8773542583      992      2/25/2014
8776712583      963      2/25/2014
8777902583      3025      2/25/2014
8004229146      2154      2/25/2014

The order by I think needs to have some case statement?
to have them listed in a certain order.

my Query is this:

SELECT DATA_1.DATA_1, Sum(DATA_1.Count) AS Counts, #2/25/2014# AS CreatedDate
FROM (SELECT Switch(AUD_MOS_IVR_LOG.DATA_1 In ('1718','8882881718'),'8882881718',AUD_MOS_IVR_LOG.DATA_1
 In ('4876','4877','8008434876'),'8008434876',AUD_MOS_IVR_LOG.DATA_1 In
('7765','7766','8005437765'),'8005437765',AUD_MOS_IVR_LOG.DATA_1 In
('6583','6584','8773542583'),'8773542583',AUD_MOS_IVR_LOG.DATA_1 In
('3583','3584','8776712583','4583','4584','8774572583'),'8776712583',AUD_MOS_IVR_LOG.DATA_1 In
 ('5583','5584','8777902583'),'8777902583',AUD_MOS_IVR_LOG.DATA_1 In
 ('1530','1531','1532','8004229146'),'8004229146',True,'Error') AS DATA_1, Count(*) AS [Count] FROM AUD_MOS_IVR_LOG
WHERE (((AUD_MOS_IVR_LOG.DATE_AND_TIME_STAMP)>=#2/25/2014#
And (AUD_MOS_IVR_LOG.DATE_AND_TIME_STAMP)<=#2/26/2014#) And
((AUD_MOS_IVR_LOG.RECORD_TYPE)='CB')) GROUP BY AUD_MOS_IVR_LOG.DATA_1)  AS DATA_1
GROUP BY DATA_1.DATA_1
ORDER BY DATA_1.DATA_1;


Thank you for all your help,
lulu
lulu50Asked:
Who is Participating?
 
John_VidmarCommented:
Create a table that explicitly has the datum related to a sort-order, and sort by that field:
SELECT	a.*
FROM	(	SELECT DATA_1.DATA_1, Sum(DATA_1.Count) AS Counts, #2/25/2014# AS CreatedDate
		FROM (SELECT Switch(AUD_MOS_IVR_LOG.DATA_1 In ('1718','8882881718'),'8882881718',AUD_MOS_IVR_LOG.DATA_1
		In ('4876','4877','8008434876'),'8008434876',AUD_MOS_IVR_LOG.DATA_1 In
		('7765','7766','8005437765'),'8005437765',AUD_MOS_IVR_LOG.DATA_1 In 
		('6583','6584','8773542583'),'8773542583',AUD_MOS_IVR_LOG.DATA_1 In
		('3583','3584','8776712583','4583','4584','8774572583'),'8776712583',AUD_MOS_IVR_LOG.DATA_1 In
		('5583','5584','8777902583'),'8777902583',AUD_MOS_IVR_LOG.DATA_1 In 
		('1530','1531','1532','8004229146'),'8004229146',True,'Error') AS DATA_1, Count(*) AS [Count] FROM AUD_MOS_IVR_LOG 
		WHERE (((AUD_MOS_IVR_LOG.DATE_AND_TIME_STAMP)>=#2/25/2014#
		And (AUD_MOS_IVR_LOG.DATE_AND_TIME_STAMP)<=#2/26/2014#) And 
		((AUD_MOS_IVR_LOG.RECORD_TYPE)='CB')) GROUP BY AUD_MOS_IVR_LOG.DATA_1)  AS DATA_1
		GROUP BY DATA_1.DATA_1
	) a
JOIN	(	select	DATA_1 = '8882881718'
		,	SortOrder = 1
		union all select '8008434876',2
		union all select '8005437765',3
		union all select '8773542583',4
		union all select '8776712583',5
		union all select '8777902583',6
		union all select '8004229146',7
	) b	ON	a.DATA_1 = b.DATA_1
ORDER
BY	b.SortOrder

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
can you please explain what this "order" is, as none of the 3 columns data seems to be ordered?

I presume you will need a "table" that defines which "DATA_1" will be displayed in which order, and join to that table, and order by that table's data.

create and fill such a table:
create table DATA_1_ORDER ( DATA_1 varchar(20), display_order int )
INSERT INTO DATA_1_ORDER ( DATA_1 , display_order   ) VALUES ( 8882881718    , 1 ) INSERT INTO DATA_1_ORDER ( DATA_1 , display_order   ) VALUES ( 8008434876    ,  2 )
INSERT INTO DATA_1_ORDER ( DATA_1 , display_order   ) VALUES ( 8005437765    ,  3 )
INSERT INTO DATA_1_ORDER ( DATA_1 , display_order   ) VALUES ( 8773542583     ,  4 )
INSERT INTO DATA_1_ORDER ( DATA_1 , display_order   ) VALUES ( 8776712583      ,  5 )
INSERT INTO DATA_1_ORDER ( DATA_1 , display_order   ) VALUES ( 8777902583      ,  6 )
INSERT INTO DATA_1_ORDER ( DATA_1 , display_order   ) VALUES ( 8004229146     ,  7 ) 

Open in new window


and your query becomes:
SELECT sq.DATA_1, sq.Counts, sq.CreatedDate
FROM (
SELECT DATA_1.DATA_1, Sum(DATA_1.Count) AS Counts, #2/25/2014# AS CreatedDate
FROM (SELECT Switch(AUD_MOS_IVR_LOG.DATA_1 In ('1718','8882881718'),'8882881718',AUD_MOS_IVR_LOG.DATA_1
 In ('4876','4877','8008434876'),'8008434876',AUD_MOS_IVR_LOG.DATA_1 In
('7765','7766','8005437765'),'8005437765',AUD_MOS_IVR_LOG.DATA_1 In
('6583','6584','8773542583'),'8773542583',AUD_MOS_IVR_LOG.DATA_1 In
('3583','3584','8776712583','4583','4584','8774572583'),'8776712583',AUD_MOS_IVR_LOG.DATA_1 In
 ('5583','5584','8777902583'),'8777902583',AUD_MOS_IVR_LOG.DATA_1 In
 ('1530','1531','1532','8004229146'),'8004229146',True,'Error') AS DATA_1, Count(*) AS [Count] FROM AUD_MOS_IVR_LOG
WHERE (((AUD_MOS_IVR_LOG.DATE_AND_TIME_STAMP)>=#2/25/2014#
And (AUD_MOS_IVR_LOG.DATE_AND_TIME_STAMP)<=#2/26/2014#) And
((AUD_MOS_IVR_LOG.RECORD_TYPE)='CB')) GROUP BY AUD_MOS_IVR_LOG.DATA_1)  AS DATA_1
GROUP BY DATA_1.DATA_1 
 ) sq
JOIN DATA_1_ORDER do 
  ON do.DATA_1 = sq.DATA_1
ORDER BY do.display_order  

Open in new window

0
 
Patrick MatthewsCommented:
In my article here on using the Switch function in Access, I specifically provide an example of using Switch to force a custom sort order.

Of course, I also point out there that, unless this is a one-off thing, you are better off maintaining your custom sort order in a table.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
lulu50Author Commented:
John,

I have syntax error with the Join:

JOIN      (      select      DATA_1 = '8882881718'
            ,      SortOrder = 1
            union all select '8008434876',2
            union all select '8005437765',3
            union all select '8773542583',4
            union all select '8776712583',5
            union all select '8777902583',6
            union all select '8004229146',7
      ) b      ON      a.DATA_1 = b.DATA_1
ORDER
BY      b.SortOrder
0
 
John_VidmarCommented:
Looks like you are trying to place that syntax into Microsoft Access, I'm not sure of the limitations. I took your query and made it a nested-table, and joined that to a nested-table... this works in SQL Server.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
in ms access, you many need to put () around the condition:

 ON      (a.DATA_1 = b.DATA_1)
0
 
lulu50Author Commented:
I put () around the condition but still not working

it says syntax error

I need to convert John's Sql statement to access database syntax.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
in ms access, visibly the "inline views" syntax is  [  .... ]  and not ( ... )
SELECT	a.*
FROM	[	SELECT DATA_1.DATA_1, Sum(DATA_1.Count) AS Counts, #2/25/2014# AS CreatedDate
		FROM (SELECT Switch(AUD_MOS_IVR_LOG.DATA_1 In ('1718','8882881718'),'8882881718',AUD_MOS_IVR_LOG.DATA_1
		In ('4876','4877','8008434876'),'8008434876',AUD_MOS_IVR_LOG.DATA_1 In
		('7765','7766','8005437765'),'8005437765',AUD_MOS_IVR_LOG.DATA_1 In 
		('6583','6584','8773542583'),'8773542583',AUD_MOS_IVR_LOG.DATA_1 In
		('3583','3584','8776712583','4583','4584','8774572583'),'8776712583',AUD_MOS_IVR_LOG.DATA_1 In
		('5583','5584','8777902583'),'8777902583',AUD_MOS_IVR_LOG.DATA_1 In 
		('1530','1531','1532','8004229146'),'8004229146',True,'Error') AS DATA_1, Count(*) AS [Count] FROM AUD_MOS_IVR_LOG 
		WHERE (((AUD_MOS_IVR_LOG.DATE_AND_TIME_STAMP)>=#2/25/2014#
		And (AUD_MOS_IVR_LOG.DATE_AND_TIME_STAMP)<=#2/26/2014#) And 
		((AUD_MOS_IVR_LOG.RECORD_TYPE)='CB')) GROUP BY AUD_MOS_IVR_LOG.DATA_1)  AS DATA_1
		GROUP BY DATA_1.DATA_1
	] a
JOIN	[	select	DATA_1 = '8882881718'
		,	SortOrder = 1
		union all select '8008434876',2
		union all select '8005437765',3
		union all select '8773542583',4
		union all select '8776712583',5
		union all select '8777902583',6
		union all select '8004229146',7
	] b	ON	a.DATA_1 = b.DATA_1
ORDER
BY	b.SortOrder 

Open in new window

0
 
lulu50Author Commented:
I tried it but I still have a syntax errorAccess
0
 
lulu50Author Commented:
Thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.