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.