trying to order by multiple columns

Forum Moderators: open

Message Too Old, No Replies

trying to order by multiple columns

where am i going wrong?

giffo

5:46 am on Jul 6, 2009 (gmt 0)

I have two tables:

course (id, title, description)
courseoptions (id, courseid, duration, price)

each course can have many courseoptions and linked by course.id = courseoptions.courseid

I would like to display the course.title(s) together in order by lowest courseoptions.price

currently the closest I can attain is


id, title, courseoption.id, duration 
4  abc     5     1 
10 nat     26     1 
11 anot    13     1 
4  abc     33     2 
10  nat    22     2

using the following:

SELECT `course`.`id`, `course`.title, `courseoptions`.duration, courseoptions.id FROM `course` , `courseoptions`
WHERE (`courseoptions`.`courseid` = `course`.`id`)
ORDER BY `courseoptions`.`duration`

I am trying to get


id, title, courseoption.id, duration 
4 abc   5    1 
4 abc   33   2 
10 nat   26   1 
10 nat   22   2 
11 anot  13   1

any suggestions on what the query should be? Thank you.

rocknbil

4:41 pm on Jul 6, 2009 (gmt 0)

Welcome aboard giffo,

I would like to display the course.title(s) together in order by lowest courseoptions.price

Probable suggestions would use a combination of group by and order by. However, it may be more logical to group the courses alphabetically and together. If this is the case, this should work:

select course.id, course.title, courseoptions.duration, courseoptions.id from course, courseoptions
where courseoptions.courseid=course.id
order by course.title asc,
courseoptions.duration asc;

Presuming "duration" is what you mean by price. If not, just change "courseoptions.duration" to "courseoptions.price;" the order by field does not have to be selected.

"asc" is not really required as that's the default (I think) but is included for clarity.

[edited by: rocknbil at 4:47 pm (utc) on July 6, 2009]

rocknbil

4:41 pm on Jul 6, 2009 (gmt 0)

The site is doing that double post thingy again!

giffo

5:07 pm on Jul 6, 2009 (gmt 0)

thanks for your response,

sadly that doesn't do it, that gives me a result of course.titles in order and courses grouped in order,


duration title 
5 Advanced P 
5 Advanced R 
1 Another 
3 Another  
4 Another  
5 brown 
2 Foundation P 
1 Foundation Ra 
2 Foundation Ra  
3 Foundation Ra  
4 Foundation Ra  
5 Foundation Ra  
1 Intermediate P

what I am trying to get is,


1 Intermediate P 
1 Another 
3 Another 
4 Another 
1 Foundation Ra 
2 Foundation Ra 
3 Foundation Ra 
4 Foundation Ra 
5 Foundation Ra 
2 Foundation P  
5 brown 
5 Advanced P 
5 Advanced R

course.titles group and ordered by the lowest courseoptions.duration