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.


All sorting are alphanumeric, and follow the ascii code that means

1 decimal representation 49
N decimal representation 78 
n decimal representation 110  

1 comes before N and that before n and so on, so far as i can tell it is sorting correctly.

This link shows how you can sort the text "correctly"

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.