Solved

Multi column sort

Posted on 2011-02-11
20
349 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 84
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
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: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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

756 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