MySQL offset/order by date/limit

Question

I'm having a kind of timeline such as in twitter/facebook, I have a main view, and I need to get the latest updates (timer), and be able to get previous updates (scrolling down). I limit the main view to 25 updates.

I have these columns (more but not relevant)

col_id - auto increment
col_date - timestamp
col_content the content

Basically with queries like these:

SELECT *
FROM table
WHERE col_id < $col_id
ORDER BY com_id DESC
LIMIT 0, 25

Currently, I'm sorting by col_id, and this way I can send the highest col_id used to get the latest updates (e.g. WHERE col_id > $col_id), and I have the smallest col_id to get previous updates (WHERE col_id < $col_id)

But I need everything to be sorted by date. But then I can't use the col_id to gather new/previous updates. So I could use the date, however if there are multiple columns with the exact same timestamp, and the 25 limit cuts it somewhere in the middle, I can't use WHERE col_date > $col_date, because I would miss updates.

I could use WHERE col_date >= $col_date, but then I need to ignore the dupes from the new ones with the current ones. I can also imagine a situation where there are over 25 updates with the same timestamp, and then I would be getting nowhere.

Is there some way I can combine the date and col_id? Or maybe a new column? I don't know how to tackle this issue.

Thanks!

This answers your question - http://stackoverflow.com/questions/514943/php-mysql-order-by-two-columns — om_deshpande, Sep 20 '13 at 12:52

score 1 · Accepted Answer · answered Sep 20 '13 at 16:02

You should record both latest col_date and col_id, and use them like this:

SELECT  *
FROM    mytable
WHERE   (col_date, col_id) < ($latest_col_date, $latest_col_id)
ORDER BY
        col_date DESC, col_id DESC
LIMIT 25

Create a composite index on (col_date, col_id) for this to work fast.

score 0 · Answer 2 · answered Sep 20 '13 at 12:54

0

The ORDER BY clause accepts multiple arguments

try this:

ORDER BY col_data DESC,col_id DESC

This will sort by date and then by id.

In addition to that you could also get 100 records and then just filter the ones till you get your result.

answered Sep 20 '13 at 12:54

Mordalthunder

256
1
3

score 0 · Answer 3 · edited May 23 '17 at 12:11

0

Yes, you can order by two columns.

SELECT *
FROM table
WHERE col_id < $col_id
ORDER BY col_date DESC, com_id DESC
LIMIT 0, 25

This question has already been answered here. Have a look - PHP MySQL Order by Two Columns

edited May 23 '17 at 12:11

Community

1
1

answered Sep 20 '13 at 12:54

om_deshpande

665
1
5
16

But I don't think that's going to solve it, if I use col_id in the WHERE I'm going to exclude rows by the id, while it's being sorted by date -- a row with a higher or lower col_id could have a higher/lower date. – Arie Sep 20 '13 at 13:06

score 0 · Answer 4 · answered Sep 20 '13 at 12:57

0

Why can't you use col_id for the issue?

One possibility is to use another column - the same way they do on reddit. If you go on first page, and click next, you see the url: ?count=25&after=t3_1mr2nk.

answered Sep 20 '13 at 12:57

jussist

11
4

I think I cannot use it, because if I use col_id in the WHERE, it would cut off certain rows based on their col_id, while the results are being sorted on date. So I need somehow to have it sort on date, but also know where it left of the previous time. Maybe I'm missing something obvious or maybe there isn't a simple solution – Arie Sep 20 '13 at 13:12

score 0 · Answer 5 · answered Sep 20 '13 at 15:48

0

SELECT *
FROM table
WHERE col_date <= $col_date AND (col_date < $col_date OR col_id < $col_id)
ORDER BY cold_date DESC, col_id DESC
LIMIT 0, 25

answered Sep 20 '13 at 15:48

Naktibalda

13,705
5
35
51

I'm guessing this also just doesn't fix it, as the col_ids and col_dates are not in the same order, – Arie Sep 20 '13 at 16:03

MySQL offset/order by date/limit

5 Answers5