How to Sort Records

[spurfilm]

I have a mySQL database of individuals who do several different activities at various start dates in the year. I need to sort all the data by start date. But because each record contains several activities and associated start dates, how can I best extract this data for sorting?

For example, a typical record...

Name
Address
Contact
Activity1
StartDate1
Activity2
StartDate2
Activity3
StartDate3
etc...

I want to sort all the activities by their StartDate and be able to access the associated Name and Address etc.

Any ideas?
Rick

[BitBlt]

Not sure how you'd do it in mySQL specifically (there may be syntactical differences), but using ANSI standard SQL you could just do a UNION query isolating each pair, sort of like this:

select name, address, contact, activity1, startdate1 as 'myDate' from myTable
UNION
select name, address, contact, activity2, startdate2 as 'myDate' from myTable
UNION
select name, address, contact, activity3, startdate3 as 'myDate' from myTable
UNION
select name, address, contact, activity4, startdate4 as 'myDate' from myTable
UNION
select name, address, contact, activity5, startdate5 as 'myDate' from myTable
UNION
select name, address, contact, activity6, startdate6 as 'myDate' from myTable
order by myDate

You may have to tweak the statement to get it to run, but you get the idea.

Another possibility is to extract data into more normalized tables (contact table and activity table), create a join and sort that way.

Hope this helps.

[griswolf]

You need to be specific in your own mind about what a 'Start date' is, then. I see two basic options:

1. You always look at activityN's startDate
2. You base it on the earliest (or most recent) startDate for the user

Option 1 is easy. SELECT blah, blah from activities ORDER BY StartDate[I]N[/I]; Option 2 requires the use of function MIN or MAX: http://dev.mysql.com/doc/refman/5.6/en/group-by-functions.html

As an aside: It is often better to split the tables into a user table and an activities table with the user_id as a foreign key. That gives you the flexibility to add another activity without rebuilding the table, and have inactive users. Of course if activities never change and you never need the user information in a non-activity context, what you have is ok.

[spurfilm]

Many thanks. This is very useful. Thank you for your time.

Not sure how you'd do it in mySQL specifically (there may be syntactical differences), but using ANSI standard SQL you could just do a UNION query isolating each pair, sort of like this:

select name, address, contact, activity1, startdate1 as 'myDate' from myTable
UNION
select name, address, contact, activity2, startdate2 as 'myDate' from myTable
UNION
select name, address, contact, activity3, startdate3 as 'myDate' from myTable
UNION
select name, address, contact, activity4, startdate4 as 'myDate' from myTable
UNION
select name, address, contact, activity5, startdate5 as 'myDate' from myTable
UNION
select name, address, contact, activity6, startdate6 as 'myDate' from myTable
order by myDate

You may have to tweak the statement to get it to run, but you get the idea.

Another possibility is to extract data into more normalized tables (contact table and activity table), create a join and sort that way.

Hope this helps.