I am trying to retrieve a set of 5 rows from a table in a MySQL database. I want to search for a specific row and retrieve the two rows above and below that record for a total of five records.
What I was thinking was if I could get the row number pointer of the row I am searching for I could use LIMIT to select the five rows I need.
There are no "row number pointers" in MySQL, per se. There are primary key indexes, but you add those yourself. Besides, they may not relate to your needs.
Generally, what you would do is use a UNION. Select the two rows that would appear before the specific row, the specific row, and the two after all bunched together with a UNION.
SELECT COUNT(*) as rank FROM parts WHERE partID < 'xx312'
Next we are going to select all the records and add a rank field, then filter out only those records that have a rank between plus or minus two of the value we got from the previous query.
SELECT *, (SELECT COUNT(*) FROM parts p2 WHERE p2.partID < p1.partID) as partRank
FROM parts p1
WHERE partRank BETWEEN $rank-2 AND $rank+2
ORDER BY p1.partID
It is possible to do this in one query by adding subqueries into the between clause I think.
select * from table where rowid< $rowid order by rowid desc limit 2
union
select * from table where rowid= $rowid
union
select * from table where rowid> $rowid order by rowid limit 2
There are 10 types of people in this world, those who understand binary and those who don't