How can I combine the following information into 1 mysql query?


Problem :

DETAILS

I want to find the difference between start and stop times(duration) for each test taken then rank users according to the highest score and lowest duration for each test. Ultimately I will use PDO to do this in mysql. So far though I've just been working on the sql. I think I'm pretty close now, but am unsure how to combine the information into one query.

WHAT I'VE GOT(untested).

I have the following table called testresults

--------------------------------------------------------------------
| index | id |         start         |          stop        | score| 
--------------------------------------------------------------------
|   1   | 23 |   2012-06-06 07:30:20 | 2012-06-06 07:30:34  | 100  |
--------------------------------------------------------------------

In my select statement I want to get the id and the score along with the calculated duration.

I can get duration for any test taken today with the below query.

SELECT TIMESTAMPDIFF(SECOND,stop,start) AS duration
FROM testresults WHERE `start` >= DATE(NOW())

Then I sort the results based on rank. Rank is determined according to score and duration. Top place goes to the highest score with the shortest duration.I believe I can do that with something like this:

ORDER BY score DESC, duration ASC

I only want maybe 100 entries so,

LIMIT 100

QUESTION

How can I combine the above information into 1 query?



Solution :

SELECT `id`, `score`,
    TIMESTAMPDIFF( SECOND, start, stop ) AS `duration`
FROM `testresults`
WHERE `start` >= DATE(NOW())
ORDER BY `score` DESC, `duration` ASC
LIMIT 100;

This will work pretty good. You'll also need to put id & score in SELECT statement.


Mysql Tutorials

Mysql Howto..

Openshift: how import database to MySQL via RHC or SSH?

how to map resultset from mysql to pojo using hibernate?

How to select the lastest record from a group in MySQL table using SQl syntax

How to import excel stored data in MySQL and keep date format as yyyy-mm-dd hh:mm:ss

PHP + PDO + MySQL: how do I return integer and numeric columns from MySQL as integers and numerics in PHP?

How to create a mysql query for the following?

how to get count in groupby mysql?

How can I combine the following information into 1 mysql query?

Mysql : How to find non duplicate rows with left join?

how to get count from two tables without subquery in mysql

How to best create breaks in a comment using PHP/MYSQL?

How to search for particular strings between commas in MySQL?

How to Remove float:right; from a MySQL table column?

How to run append query from data macro MS Access?

How to mysql database encrypt and use in Coldfusion

How to make a select statement PDO and end up with $row['field']

MySQL how to add days to column

How do I add a column to an existing FULLTEXT index?

How to lock mysql tables in php

How to get error code returned by query using MySQL in Asp.Net

Mysql: how to structure this data and search it

How to do this in a mysql query

How to replace a column with null values in mysql

How to get the start Unix timestamp of current hour in MySQL?

How to handle special characters like semi colons and quotation marks in php

MySQL WHERE: how to write “!=” or “not equals”?

How to echo MySQL results in inverse order?

How to expose mysql data as a soap web service in Talend

How do I display a quote for 24 hours

how to add 2 columms in mysql?