I have a resultset from a SELECT query, based on a certain ORDER BY condition, and I’m trying to check the integrity of the order in which the resultset is rendered. First, I’m specifying a LIMIT, on the basis of which, the resultset size is limited. Then, I’m selecting a random PK value from the resultset, get a particular row and use the comparison operator on that row value(s), for the next SELECT. Something like this(Let’s suppose a is the random row from above):
SELECT * FROM tbl where tbl_columnX > a.fieldX and tbl_columnY > a.fieldY
ORDER BY certain fields
Basically, what I’m trying to do here, is to ensure that for a resultset (R) of certain order, when choosing a random PK value, and issuing a SELECT statement with comparison operator(s) on that PK based row value(s), the resulting set(S) contains the rows that are in R, after/before the PK, depending whether the order is ascending or descending.
It is basically working, except for a few cases where the next row(s) after the PK based row, have the same value(s) as the PK based row, in a few column(s). For example, these are the last few rows, from the resultset R:
ID(varchar) VALUE(varchar) DESC1(text) DESC2(text) NAME(text)
------------- ------------- ------------- ------------- -------------
62E212AF C09A F/T/K AH AN
CAFFA217 C09AA F/T/K 2 Ah An
D90222CC C09BA F/T/K 2d Ahod Anod
Now, if I try to get a new resultset(S) after the ID 62E212AF, it skips the ID CAFFA217, and the first ID in the resultset is D90222CC. The similar pattern is repeated in a few other places. This is how I’m doing the order by, in this particular case:
order by
NAME asc,
VALUE asc,
DESC1 asc,
DESC2 asc,
ID asc
I’d really appreciate some help with this.
