Solved

Query sort order question

Posted on 1998-07-31
8
295 Views
Last Modified: 2010-04-04
Hi

Hope someone can help.

I want to define a specific sort order a query will return on a Paradox table.

The field that I'm trying to sort(correctly) is the day field i.e Monday, Tuesday....Sunday.

I have a table which hold staff accident records, the index is auto-increment and I don't want to change the table structure. I can set the query to Order By ...and it sorts no problem, but of course it sorts alphabetically which is of no use in this instance.

The sort turns out as Friday, Monday,Thursday etc, I really need the sort to be the logical Monday, Tuesday, Wednesday....

Anyone know how to do this ?

Thanks in advance

John  
0
Comment
Question by:tam97
  • 4
  • 4
8 Comments
 
LVL 5

Accepted Solution

by:
mayhew earned 100 total points
ID: 1359386
The way I have done this is to create another table that contains the name of the day and a number representing the search order.

Example:

Monday     1
Tuesday     2
etc.

Then do a join on the day field in your table with the day name in the new table.

Add on an order by NewTable.Sequence and you'll be good to go.

Scripts I used to test this in MS_SQL:

create table test1(
sequence integer,
day varchar(25) )

create table test2(
dayname varchar(25),
sortorder integer)

insert into test1 values (1,'friday')
insert into test1 values (2,'monday')
insert into test1 values (3,'saturday')
insert into test1 values (4,'thursday')
insert into test1 values (5,'sunday')
insert into test1 values (6,'tuesday')
insert into test1 values (7,'wednesday')

insert into test2 values ('monday',1)
insert into test2 values ('tuesday',2)
insert into test2 values ('wednesday',3)
insert into test2 values ('thursday',4)
insert into test2 values ('friday',5)
insert into test2 values ('saturday',6)
insert into test2 values ('sunday',7)


select day from test1 a, test2 b
where a.day=b.dayname
order by b.sortorder


Hope this helps!

Don
0
 

Author Comment

by:tam97
ID: 1359387
Hi Mayhew

Thanks for your reply.

I adapted your answer to my program, your've got me onto the right track.

The query result has'nt came out exactly as I had hoped, could you check the following code and advise.

SELECT *
FROM  AccidentTbl.DB, DayTbl.DB
Where AccidentTbl.db.FirstDay=DayTbl.db.Dayname
Order by DayTbl.db.SortOrder

The last line Order by.......SortOrder produced the following error,"Field in order by must be in result set", changing it to DayTbl.db.Dayname did allow the query to operate.

But the result was incorrect, it did add the two fields from the DayTbl, e.g. SortOrder and Dayname but produced 7 copies of each record and not in the logical order.

Any ideas where I have gone wrong.

Rgds
John
0
 
LVL 5

Expert Comment

by:mayhew
ID: 1359388
I'm not sure what the problem is.  If you're doing a 'SELECT *' then SortOrder *should* be in the result set.

I'm going to need to know more.  Could you provide the table descriptions (for the relevant fields at least) and a couple rows of data from each?  Display them exactly as they appear, if you would, and I'll bet we can figure it out.  Please include column names and attributes (eg. varchar(20), etc.).

BTW, the reason you are getting 7 copies of all your records is that your join is failing for some reason so you're getting a full outer join (the 7 rows in your DayTbl times the number of rows in your AccidentTbl).

Looking forward to your reply.    :)
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:tam97
ID: 1359389
Hi Mayhew

Thanks for replying.

I said that you had put me onto the right track, well I managed to get round the problem by adding an extra field into the accidentbl, therefore having a sort order within the result table.

But I have to say I know this is just a patch up and would like to find out the correct way to achieve the desired results, so if it's ok with you I'll take up your offer and supply the table details to you.

I'll send them out tonight as I'm just back from my mates house and it's 2.45am way past my bed time !

Thanks for your time.

Best Regards
John
0
 
LVL 5

Expert Comment

by:mayhew
ID: 1359390
That's fine.  :)

Feel free to post them here or to e-mail them to me.

don@mayhewnet.com
0
 

Author Comment

by:tam97
ID: 1359391
Don

I'm very sorry for not getting back in touch but my system has been down for a while now, and I have just got the motherboard replaced, and am therefore back on line.

I'd like to thank you for your help and award the points to you.

Thanks again.

Regards John
0
 
LVL 5

Expert Comment

by:mayhew
ID: 1359392
Great, thanks John.  :)

Did you ever get it completely resolved?  I'm still willing to take a look at it if you want me to.

Don
0
 

Author Comment

by:tam97
ID: 1359393
Hi Don

Thanks for your reply, I didn't wish to bother you further, but if you don't mind I'll e-mail you the table structures shortly.

Thanks again for all your help.

John
0

Featured Post

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Delphi XE10 Round Image 2 184
Dynamically Created Query 3 69
Sending files from  idTcpServer Socket to idTcpClient 2 52
MS Access from Delphi 31 78
Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question