Solved

Multi column sort

Posted on 2011-02-11
20
351 Views
Last Modified: 2012-05-11
I have a database with 4 columns
id  name birthday  passingDay

I already have a select that gets the records for the current month and sorts by birthday

How do I sort the records so the they are sorted by day of month based on birthday AND passing?

So...if there are two records and one has a birday of 01/02/1948 and a second record has a date of passing of 01/02/2010

My sort would place them both after the records where day of birthday/passing = 1first and before all records with a day of month for birthday/passing = 3

Then they'd be sorted by name after the day on month(s)

I needd this in bth SQL and MSAccess
0
Comment
Question by:lrbrister
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
  • 3
  • +4
20 Comments
 
LVL 85
ID: 34871387
Your SELECT Statement can ORDER BY multiple fields:

SELECT * FROM MyTable ORDER BY Birthday, PassingDay

0
 
LVL 7

Expert Comment

by:tlovie
ID: 34871401
I'm not exactly clear on what you want, but if you have an order by clause, you just list the columns there that you want.

<your existing select statement>
ORDER BY DAY(birthday), DAY(passingday), Name
0
 
LVL 15

Expert Comment

by:tim_cs
ID: 34871444
Might need a little more detail because I'm not 100% sure of what your asking but maybe this is what you're after?

ORDER BY
   CAST(DAY(Birthday) AS FLOAT)/CAST(DAY(PassingDay) AS FLOAT)
0
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 

Author Comment

by:lrbrister
ID: 34871698
Take a look at my screen guys (MSACCESS)

I'm trying the sort it just isn't doing it.

MS Access SQL attached screenprint
SELECT datatable.lastName, datatable.firstName, datatable.childrenNames, IIf(Month(datatable.Birthday)=Month(Now()),datatable.BirthDay,Null) AS Birthday, IIf(Month(datatable.Passing)=Month(Now()),datatable.Passing,Null) AS Passing, datatable.Phone
FROM datatable
WHERE (((Month(datatable.Birthday))=Month(Now()))) Or (((Month(datatable.Passing))=Month(Now())))
ORDER BY DAY(datatable.Birthday), DAY(datatable.Passing), datatable.lastname;

Open in new window

0
 

Author Comment

by:lrbrister
ID: 34871703
It's like the nulls are causing a problem
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 34872260
order by day(nz(birthday,dateofpassing),lastname
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 34872263
order by day(nz(birthday,dateofpassing)),lastname
0
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 34872434
I don't know if we understand yet what you are after here.  You say birthday/passing as if this was a single field, but there are two fields here, which generally contain different dates.  Also, you give an example where the month is the same in both fields, which would not generally be the case.  Please explain the sort you want, very specifically.
0
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 34872444
I think it is pretty clear that it is not just a matter of putting 3 fields in a certain order in a query, but would involve calculated expressions of some sort.
0
 

Author Comment

by:lrbrister
ID: 34872474
Helen_Feddema:
Thanks for your reply and sorry for the lack of clarity.

I need to sort by the day of month for birthday AND passing...that's two columns...then lastName

So...the year can be any year
The month is always set as current month
so...the select (sort) would grab all 1st day of the month records...whether they are a birthday OR a passing and place them at the top of the output sorted by lastname

then 2nd day of month if any, then 3rd day etc...
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 34872497
assuming that birthday and passing date are mutually exclusive then as i've already stated

you want an order by  of the form...

order by day(nz(birthday,dateofpassing)),lastname


which use day of birthday unless birthday  is null in which case it uses date of passing for the day....
0
 
LVL 15

Expert Comment

by:tim_cs
ID: 34872614
Most of the records only have one date but a few have a birthdate and passingdate.  What do you want to order by in those cases?  The earliest day?  So for SQL something like this?

ORDER BY
   CASE WHEN DAY(Birthday) < DAY(PassingDay) OR DAY(PasingDay) IS NULL THEN DAY(BIRTHDAY)  ELSE DAY(PassingDay) END
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34876626
>>Zones: SQL Server 2005, MS SQL Server, Microsoft Access Database
Take a look at my screen guys (MSACCESS)<<

So why post in the SQL Server zones.  Were you looking for a T-SQL solution?
0
 

Author Comment

by:lrbrister
ID: 34887289
Lowfatspread:
I think your statement is really close.

in my attached screen print though I have 2/4/yyyy row highlighted which should be higher in the passing column Screen Print Sort Order
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 34887540
ok...

with thanks to tim_cs

try this
order by day(iif(day(nz(birthday,passingdate))<day(nz(passingdate,birthday))
         ,nz(birthday,passingdate)
         ,nz(passingdate,birthday)),lastname

Open in new window

0
 

Author Comment

by:lrbrister
ID: 34887696
Lowfatspread:
Sorry....Still not there. Modified your statement for actual column names
Added closing ")" for IIF
Coda attached

For this output
 Lowfatspread Screenshot
SELECT datatable.lastName, datatable.firstName, datatable.childrenNames, IIf(Month(datatable.Birthday)=Month(Now()),datatable.BirthDay,Null) AS Birthday, IIf(Month(datatable.Passing)=Month(Now()),datatable.Passing,Null) AS Passing, datatable.Phone
FROM datatable
WHERE (((Month(datatable.Birthday))=Month(Now()))) Or (((Month(datatable.Passing))=Month(Now())))
order by day(iif(day(nz(birthday,passing))<day(nz(passing,birthday))  
         ,nz(birthday,passing)  
         ,nz(passing,birthday))),lastname

Open in new window

0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 500 total points
ID: 34887863
hmmm...
ok didn't realise that was how you were doing it

how does this work out

SELECT lastName, firstName, childrenNames
, IIf(Month(Birthday)=Month(Now()),BirthDay,Null) AS Birthday
, IIf(Month(Passing)=Month(Now()),Passing,Null) AS Passing
, Phone
FROM datatable
WHERE Month(Birthday)=Month(Now())
   Or Month(Passing)=Month(Now())
order by day(iif(day(nz(IIf(Month(Birthday)=Month(Now()),BirthDay,Null),IIf(Month(Passing)=Month(Now()),Passing,Null)))
<day(nz(IIf(Month(Passing)=Month(Now()),Passing,Null),IIf(Month(Birthday)=Month(Now()),BirthDay,Null)))  
         ,nz(IIf(Month(Birthday)=Month(Now()),BirthDay,Null),passing)  
         ,nz(IIf(Month(Passing)=Month(Now()),Passing,Null),birthday))),lastname

Open in new window

0
 

Author Closing Comment

by:lrbrister
ID: 34892460
Way to go Lowfatspread:!

remind me to buy you a sody-pop sometime!
0
 

Author Comment

by:lrbrister
ID: 34892470
follow-up

On the solution I was getting an alias error on the "AS Birthday and passing at the top
so I just changed to AS outBirthDay and ASoutPassing
0
 
LVL 15

Expert Comment

by:tim_cs
ID: 34892606
Irbrister,

Glad you found your solution. Just wanted to point out that if what you posted is live production data you might want to ask a mod to delete your images.  Your clients/employees might not be happy if their info is shared on the Internet even if it is just names and telephone numbers.  
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

630 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