I can't sort by date, priority

Databases
1

Does anyone know why I can’t sort by dateScheduled, priority?

order by dateScheduled, priority

The priority column is a type of INT. Any ideas?

Thanks!

2

It would be easier if we could see your query but if you’re using priorities to order data, then I’m afraid there’s little that you can do

3

[quote=“busboy, post:1, topic:110587, full:true”]
Does anyone know why I can’t sort by dateScheduled, priority?[/quote]

that looks fine

must be some other reason

4

Then I stand corrected

5

Here is the full query:

select
u.uID,
u.firstName,
u.lastName,
u.subscription,
date_format(n.dateScheduled, ‘%m-%d-%Y’) as dateScheduled,
n.nID,
n.priority,
n.type,
n.format,
n.message,
n.messageHTML
from users u, notifications n
where
n.uID = u.uID
and bouncing = ‘No’
and dateSent is null
and n.error is null
and date(dateScheduled) <= curdate()
order by priority
limit 20

Thanks!

6

give your formatted date a different alias, then put the new alias into the ORDER BY

a helpful tip: qualify all columns used in the query, not just some of them

another tip: use INNER JOIN

7

r937, I took your advice and fixed things up, but it’s still not sorting correctly. It’s correctly putting the rows with the oldest date first, but not also sorting by the priority column. The priority column simply uses 1, 2, or 3.

Thoughts?

SELECT 
	u.uID, 
	u.firstName, 
	u.lastName, 
	u.subscription, 
	u.memberType, 
	u.memberNumber,
	date_format(n.dateScheduled, '%m-%d-%Y') as scheduledFormatted, 
	n.nID, 
	n.priority, 
	n.type, 
	n.format, 
	n.sender, 
	n.recipient, 
	n.subject, 
	n.message, 
	n.messageHTML
FROM 
	users u 
INNER JOIN
	notifications n 
ON
	n.uID = u.uID
WHERE 
	u.bouncing = 'No'
	and n.environment = 'www' 
	and n.dateSent is null  
	and n.error is null 
	and n.sender <> ''  
	and n.recipient <> ''  
	and date(n.dateScheduled) <= curdate()
ORDER BY
	dateScheduled, priority
LIMIT
	8000
8

you’re sorting by the actual datetime, not the day

work it out…

:slight_smile:

9

okay, a hint…

n.dateScheduled is a datetime, while scheduledFormatted, which is the alias given to the result of the DATE_FORMAT function, is a date

you want to ORDER BY the date

1 Like
10

Don’t let this go to your head or anything, but you are amazing and truly have a gift! This was a good catch on your part.

11

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.