ORDER BY the quantity of records with a timestamp of < 24 hours (MySQL)

Question

I have a table full of entries with DATETIME time stamps, and an ID field. I want to make a MySQL query (in my PHP script) that selects from another table with the matching ID. I want to make some kind of join that will sort by the number of entries in the first table with a timestamp more recent than 24 hours. So basically, if there are 30 entries in the first table with a timestamp of less than 24 hours with the ID "334" then I want to select the row from the second table with the ID of 334. And that should come before an entry with the ID "234" that only has 20 entries within the last 24 hours.

I hope this is clear... I'm really stumped on how to do this, so thanks for any help. :D

Answer 1

Try:

Select b.ColA, b.ColB, b.ColC,-- etc.
  Count(*) count
From TableB b 
   Join TableA a
     On a.Id = b.Id
        And a.TimeStamp > getDate() - 1
Group By b.ColA, b.ColB, b.ColC -- etc.
Order By Count(*) Desc

If you also want to see the rows from TableB that have no timestamps in the past 24 hours then use an outer join:

 Select b.ColA, b.ColB, b.ColC,-- etc.
  Count(*) count
 From TableB b 
   Left Join TableA a
     On a.Id = b.Id
        And a.TimeStamp > getDate() - 1
 Group By b.ColA, b.ColB, b.ColC -- etc.
 Order By Count(*) Desc

Answer 2

Use:

  SELECT a.*, 
         x.num
    FROM TABLE_A a
    JOIN (SELECT t.id,
                 COUNT(*) AS num
            FROM TABLE_B t
           WHERE t.timestamp BETWEEN DATE_SUB(NOW, INTERVAL 1 DAY)
                                 AND NOW()
        GROUP BY t.id) x ON x.id = a.id
ORDER BY x.num DESC