Jump to content


Photo

How to order by weekday (but not alphabetically!) ?


  • Please log in to reply
5 replies to this topic

#1 timcclayton

timcclayton
  • Members
  • PipPip
  • Member
  • 10 posts

Posted 16 July 2006 - 06:05 PM

Hello all.
I am using a SELECT query to drag out records sorted by WEEKDAY. The trouble is, they sort themselves alphabetically (naturally) and this looks a bit stupid when Friday is the "first" day. Is there a way to sort by actual weekday order?

Code I am using is:

"SELECT * FROM FLIGHTS ORDER BY 'intdom', 'day' ASC"

where "day" is the weekday field.

#2 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,025 posts

Posted 16 July 2006 - 06:18 PM

"SELECT * FROM FLIGHTS ORDER BY intdom, 
	CASE 
		WHEN day='Sunday' THEN 0
		WHEN day='Monday' THEN 1
		WHEN day='Tuesday' THEN 2
		WHEN day='Wednesday' THEN 3
		WHEN day='Thursday' THEN 4
		WHEN day='Friday' THEN 5
		WHEN day='Saturday' THEN 6
	END"

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#3 effigy

effigy
  • Staff Alumni
  • Advanced Member
  • 3,600 posts
  • LocationIL

Posted 16 July 2006 - 06:40 PM

or date_format(day, '%w')
Regexp | Unicode Article | Letter Database
/\A(e)?((1)?ff(?:(?:ig)?y)?|f(?:ig)?)\z/

#4 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,025 posts

Posted 16 July 2006 - 06:46 PM

I got the impression that 'day' column contained the day name and was not a DATE column
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#5 effigy

effigy
  • Staff Alumni
  • Advanced Member
  • 3,600 posts
  • LocationIL

Posted 16 July 2006 - 06:49 PM

Good point. I'll go wake up... :)
Regexp | Unicode Article | Letter Database
/\A(e)?((1)?ff(?:(?:ig)?y)?|f(?:ig)?)\z/

#6 timcclayton

timcclayton
  • Members
  • PipPip
  • Member
  • 10 posts

Posted 16 July 2006 - 07:00 PM

Yes that's correct, the first example worked beautifully. Thanks very much Barand (and effigy too for trying to help) - much appreciated!




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users