0

We have this on mysql:


**And want return with 2 limit:**
1- "SELECT * FROM table ORDER BY point DESC LIMIT 0,2" //return 'google' & 'jsfiddle'
2- "SELECT * FROM table ORDER BY point DESC LIMIT 2,2" //return 'stackoverflow' & 'msn'
3- "INSERT INTO table SET id = 11, name = 'gmail', point = 101 "
4- "SELECT * FROM table ORDER BY point DESC LIMIT 4,2" //return 'msn' & 'emra'
**in number 4 'msn' is duplicate. I want number 4 return 'emra' & 'facebook'.**
I can save 'id' and I want query that return records after that id.

[EDIT] let me ask in another way:

we have this records:

id, name, point
4, 'google', 100
6, 'yahoo', 100
3, 'gmail', 100
8, 'ymail', 100
2, 'fb', 100

I have id:3 and want select records that are after this id(in this example 'ymail', 'fb')



this is a ajax load content by scorlling(like facebook) but about max points of users that every moment is changing.

Glorfindel
  • 21,988
  • 13
  • 81
  • 109
EmRa228
  • 1,226
  • 13
  • 22

1 Answers1

2

If i understand your question correctly (which i doubt), You want a way of fetching additional top results matching your search criteria, after getting first N...

The major problem with this is that the new record (which you show in #3) may go anywhere between the previous results, thus making the records you already fetched obsolete (in order).

So, if you really want this, you should do two things:

  1. Remember all the IDs you already fetched, and in query provide NOT IN(list of already fetched ids) in WHERE clause, without specifying offset (only LIMIT 2)

  2. After you fetched that, add the amount of the results you fetched anew to the list and reorder it if needed (some sort of insertion sort)

poncha
  • 7,726
  • 2
  • 34
  • 38
  • This solution is correct, but may the IDs I already fetched was too much and need heavy upload traffic from the client. There is a better solution? – EmRa228 Jul 11 '12 at 11:45
  • 2
    @EmRa228 You can remember the list of IDs on the server (in server-side session storage for instance), and on next request update the list again. However, notice that this approach contradicts base principle of RESTful services - these requests are no longer stateless – poncha Jul 11 '12 at 12:54
  • This approach is very intelligently. I also can store in the database. I'll be waiting, perhaps someone gave me SQL statement it. – EmRa228 Jul 11 '12 at 14:09