MySQL query not offsetting correctly

Question

Can someone help me understand why the following query is not offsetting correctly?

It's meant to select all records in the games table, and add a column with a value of 0 or 1 based on whether a record in another table (wishlist) exists with the same gameId @memberId (in plain English, get me all records from games, and mark any game that exists in the wishlists table, under whatever memberId I give you)

SELECT *,
    CASE WHEN wishlists.memberid IS NULL THEN 0 ELSE 1 END AS InMembersList

FROM games
    INNER JOIN platforms ON games.platformid = platforms.id 
    LEFT OUTER JOIN wishlists ON games.id = wishlists.gameid and wishlists.memberid = @memberId

WHERE platforms.platformUrlId = @platformUrlId

ORDER BY releaseDate DESC

LIMIT 1,8

When I change the offset from 1 to 2, or 3, or whatever, many of the same records appear, which does not make any sense. Where am I going wrong?

Schema:

platforms(id, platform)
members(id, name)
games(id, platformId, releaseDate)
wishlists(id, memberId, gameId)

Mchl · Accepted Answer · 2010-09-28 19:14:12Z

2

LIMIT 1,8 means start from row number 1 (they start from 0) and fetch 8 rows. So LIMIT 2,8 will give you 8 rows starting from row 2 - seven of which will be the same as with LIMIT 1,8

answered Sep 28, 2010 at 19:14

Mchl

62.4k9 gold badges121 silver badges121 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mohamad Over a year ago

Complete muppetry on my part! I forgot I was meant to multiply offset * limit to get the correct pagination!

Collectives™ on Stack Overflow

MySQL query not offsetting correctly

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related