Query Syntax help

NZ_Kiwis

I've got a MySQL database with 3 columns in it, they are: ID, NewsID and TimeStamp I want to display the top 5 rows with the most ID's with the timestamp in the last 24 hours.

So ID 22 has 12 rows, 323 has 9 rows etc etc

djjjozsi

NZ_Kiwis;10972522 wrote:
So ID 22 has 12 rows, 323 has 9 rows etc etc

means you have a duplicated table?

how to smart better:
Hi evrybody, i have a table:
SQL dump goes here.

It has example data:
SQL dump with limited amount or records...

This table has a 1:N relation to another table (news newsid is NEWID in my table as foreign key...). I want to group and count news by time with limitation from the last 24 hours.

NZ_Kiwis

Sorry?
My table is as follows

ID | NewsID | TimeStamp

1 | 353 | 1295336477
2 | 353 |1295336477
3 | 223| 1295336477
4 | 141 |1295336477
5 | 136 | 1295336477
6 | 223 |1295336477

so the top 5 would be 353, 223 141 and 136 i that order

bradgrafelman

First, I would highly recommend you change the 'TimeStamp' column to be of type DATETIME - it's much easier to work with 'YYYY-MM-DD HH:MM:SS' than it is a large integer.

Second, you should add a 'GROUP BY' clause to group the results by NewsID values, and then you can use an 'ORDER BY' statement to order by 'COUNT(NewsID)'.

djjjozsi

NZ_Kiwis;10972526 wrote:
Sorry?
My table is as follows

ID | NewsID | TimeStamp

1 | 353 | 1295336477
2 | 353 |1295336477
3 | 223| 1295336477
4 | 141 |1295336477
5 | 136 | 1295336477
6 | 223 |1295336477

so the top 5 would be 353, 223 141 and 136 i that order

To start, i were count the newsids.

SELECT `NewsID`,COUNT(`NewsID`) FROM `table` GROUP BY `NewsID` ORDER BY COUNT(`NewsID`) DESC LIMIT 5

Now, the where condition would be tricky if you have timestamps only. you should use date time instead.
or add a where condition like this:

$time=time()-24*60*60;
$sql="..........   WHERE TimeStamp > $time";

Or you should search for the date/time functions in mysql.com to make a proper where condition.

Res

The date portion isn't that hard, MySQL has some handy functions to simplify this.

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

WHERE `TimeStamp`  >= UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 1 DAY))

In this bit...

UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 1 DAY))

... The first function (UNIX_TIMESTAMP) converts the date to the timestamp format you are using in the table. Then DATE_SUB allows us to modify a date, in this case our date is NOW(), the current date, our interval is 1 day, so we are getting the current date -1 days, or the past 24 hours.

I second what Brad said regarding using date time as the column type. Make life easier for yourself and convert it. If you do simply remove the wrapping UNIX_TIMESTAMP() function from what I posted and it should work for date time fields.