Solved

Multi column sort

Posted on 2011-02-11
20
347 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
  • 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
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 …

810 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