mysql选择2个表并按日期排序1个（更接近当前时间）

Question

This is my problem.

table ns_leagues:

id   name
1    League 1
2    League 2
3    League 3

table ns_upcoming:

upID   league   date
1      1        1410390000
2      2        1411990200
3      3        1412010000

I have this 2 columns and I want to select the leagues from table ns_leagues and order by the row from the ns_upcoming with the date more close to the current time.

I tried several ways but nothings is working so far.

FAIL:

SELECT id 
FROM ns_leagues 
WHERE id in (
    SELECT league 
    FROM ns_upcoming 
    WHERE date<='".$now."' 
    ORDER BY date ASC

SELECT DISTINCT id 
from ns_leagues a 
LEFT JOIN ns_upcoming v ON  a.id = v.league AND v.date<= '".$now."' 
ORDER BY v.date ASC

CURRENT:

SELECT * FROM ".PREFIX."leagues ORDER BY id DESC

This is what I have right now (on the right side: PROXIMOS PARTIDOS): http://www.nuno-silva.pt/jobs/mark/index.php

"En vivo" means that date >= time(); and it's not finished. The times are countdowns for the match.

I need to order the leagues by date (selecting for that the row with the closest date to time(); from column date of table ns_upcoming of the selected league)

Could you give me a help on this one please?

Answer 1

You'd need to sort by the time DIFFERENCE between "now" and the match's time, e.g.

SELECT ...
...
ORDER BY DATEDIFF(ns_upcoming.date, curdate())