CODEWITHSUNDEEP
phpsqlmysqlsql-order-by
RobHardgood
RobHardgood

Reputation: 550

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

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

Upvotes: 0

Views: 411

Answers (2)

OMG Ponies
OMG Ponies

Reputation: 332691

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

Upvotes: 2

Charles Bretana
Charles Bretana

Reputation: 146557

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

Upvotes: 0

Related Questions