Solved

Multi column sort

Posted on 2011-02-11
20
343 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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
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.

758 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

13 Experts available now in Live!

Get 1:1 Help Now