CODEWITHSUNDEEP
mysqlsql
Steven Smethurst
Steven Smethurst

Reputation: 4614

Order by created time + timeout, MySQL

I have two tables, "Questions": A list of questions, "QResults" The users results to these questions. The questions have a timeout in hrs before they are asked again. 

Table: Questions
   ID
   Timeout - The amount of time before asking the question again 

Table: Results
   ID 
   Created (timestamp) - When this record was added. 
   Questions_ID - A FK to the Question table

Example data

Table: Questions
 ID | Timeout (hrs) 
--------------
  1 | 1    
  8 | 6 
 15 | 1 
 55 | 1

Table: QResults
ID | Created            | Q_ID 
-------------------------------
 1 | Jan 24, 2012 00:00 |    1 
 2 | Jan 24, 2012 06:05 |   15 
 3 | Jan 24, 2012 02:00 |    8 
 4 | Jan 24, 2012 01:00 |    1 
 5 | Jan 24, 2012 02:00 |    1

What I am looking for is a query that will sort the questions based on the last time the question was answered + the timeout. If the question has never been answered then it should be pushed to the top of the list.

For example if the query was run on the above data it would produce the following data set. 

The result of the query I am looking for. 
ID | timeout + created aka eligible
-------------------------------
55 | Jan 01, 1970 01:00 *(1) See note below*
 1 | Jan 24, 2012 03:00 *(2) See note below* 
 8 | Jan 24, 2012 07:05
15 | Jan 24, 2012 08:00

*Note: (1) It does not matter what the date is for id=55, as long as it showes up first. because currently there is not an QResults for it. (2) This has a value of 3hr as it use the newest answer creation time + timeout.

Let me put this another way. I am looking for a question that has the lowest value for (last asked + timeout). If the question has been answered 3 times it should use the newest question answered time + timeout for the eligible value. I hope this makes sense.

Upvotes: 2

Views: 1584

Answers (2)

snowguy
snowguy

Reputation: 911

I can't check the syntax right now but I'd do something like this.

  • get a lowest record for each ID in your questions table
  • append to those records all of the records in your results table plus the timeout from your question table
  • get the max for the result by ID

Syntax would be something like...

Updated syntax

select ID, MAX(Timeout) as Timeout
from
(
  (
  select ID, cast('1970-01-01 01:00' as DATETIME) as Timeout
  from Questions
  ) 
union all
  (
  select q.ID, r.created + INTERVAL q.Timeout hour as Timeout
  from QResults as r, Questions as q
  where r.q_ID=q.ID
  ) 
) c
group by ID
order by MAX(Timeout) asc

This gives me the following results. (Note the ordering difference from yours. I think this is what you meant.)

ID  Timeout
55  1970-01-01 01:00:00
1   2012-01-24 03:00:00
15  2012-01-24 07:05:00
8   2012-01-24 08:00:00

If this runs slowly, you may want to get the max date by id on your Qresults table before you do the join to the questions table to add the timeout. I wouldn't bother doing this though unless you need to as it will complicate the query and the mysql optimizer may be smart enough to figure that out on its own.

Upvotes: 1

Ray Toal
Ray Toal

Reputation: 88378

Something like this, but I'll leave it to you to optimize:

(select q.id as id, date_add(r.created, interval q.timeout hour) as eligible
from questions q, qresults r
where q.id = r.id)
union (
select q.id as id, '1970-01-01 00:00:00' as eligible
from questions q
where q.id not in
    (select id from qresults r)
)
order by 2 asc

Upvotes: 1

Related Questions