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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
The Five Tenets of the Most Secure Backup

Data loss can hit a business in any number of ways. In reality, companies should expect to lose data at some point. The challenge is having a plan to recover from such an event.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.