Solved

Multi column sort

Posted on 2011-02-11
20
346 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
 

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
[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

 
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

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

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
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…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

861 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now