Link to home
Start Free TrialLog in
Avatar of Larry Brister
Larry BristerFlag for United States of America

asked on

Multi column sort

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
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

Your SELECT Statement can ORDER BY multiple fields:

SELECT * FROM MyTable ORDER BY Birthday, PassingDay

Avatar of tlovie
tlovie

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
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)
Avatar of Larry Brister

ASKER

Take a look at my screen guys (MSACCESS)

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

MS Access SQL attached User generated image
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

It's like the nulls are causing a problem
order by day(nz(birthday,dateofpassing),lastname
order by day(nz(birthday,dateofpassing)),lastname
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.
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.
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...
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....
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
>>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?
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 User generated image
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

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

For this output
 User generated image
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

ASKER CERTIFIED SOLUTION
Avatar of Lowfatspread
Lowfatspread
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Way to go Lowfatspread:!

remind me to buy you a sody-pop sometime!
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
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.