I have a table as follows
Table item {
ID - Primary Key
content - String
published_date - When the content was published
create_date - When this database entry was created
}
Every hour (or specified time interval) I run a process to update this table with data from different sources (websites). I want to display the results according to the following rules.
1. The entries created each time the process runs should be grouped together. So the entries from the 2nd process run will always be after the entries from the first process run even if the published_date of an entry from the first run is after the published_date of an entry from the 2nd run.
2. Within the grouping by run, the entries by sorted by published_date
3. Another restriction is that I prefer that data from the same source not be grouped together. If I do the sort by create_date, published_date I will end up with data from source a, data from source b etc. I prefer that the data within each hour be mixed up for better presentation
If I add a column to this table and store a counter which increments each time the process is run, it is possible to create a query to sort first by counter and then by published_dt. Is there a way to do it without adding a field? I'm using Hibernate over MySQL.
e.g.
Hour 1 (run 1)
4 rows collected from site a (rows 1-4)
3 rows collected from site b (rows 5-7)
hour 2 (run 2)
2 row collected from site a (rows 8-9)
3 rows collected from site b (rows 10-12)
...
After each run, new records are added to the database from each website. The create date is the time when the record was created in the database. The published date is part of the content and is read in from the external source.
When the results are displayed I would like rows to be grouped together based on the hour they were published in. So rows 1-7 would be displayed before rows 8-12. Within each hourly grouping, I would like to sort the results by published date (timestamp). This is necessary so that the posts from all the sites collected in that hour are not grouped together but rather mixed in with each other.
