How do I get LIKE and COUNT to return the number of rows less than a value not in the row?

Question

For example:

SELECT COUNT(ID) FROM My_Table
WHERE ID < 

(SELECT ID FROM My_Table
WHERE ID LIKE '%4'
ORDER BY ID LIMIT 1)    

My_Table:

  X          ID    Y     
------------------------
|      |     A1  |     |
------------------------
|      |     B2  |     |
------------------------
|      |     C3  |     |
------------------------ -----Page 1
|      |     D3  |     |
------------------------
|      |     E3  |     |
------------------------
|      |     F5  |     |
------------------------ -----Page 2
|      |     G5  |     |
------------------------
|      |     F6  |     |
------------------------
|      |     G7  |     | -----Page 3

There is no data ending in 4 but there still are 5 rows that end in something less than "%4".

However, in this case were there is no match, so SQLite only returns 0

I get it is not there but how do I change this behavior to still return number of rows before it, as if it was there?

Any suggestions?

Thank You.

Answer 1

SELECT COUNT(ID) FROM My_Table
WHERE ID < (SELECT ID FROM My_Table
  WHERE SUBSTRING(ID, 2) >= 4
ORDER BY ID LIMIT 1)   

Answer 2

Assuming there is always one letter before the number part of the id field, you may want to try the following:

SELECT COUNT(*) FROM my_table WHERE CAST(substr(id, 2) as int) <= 4;

Test case:

CREATE TABLE my_table (id char(2));

INSERT INTO my_table VALUES ('A1');
INSERT INTO my_table VALUES ('B2');
INSERT INTO my_table VALUES ('C3');
INSERT INTO my_table VALUES ('D3');
INSERT INTO my_table VALUES ('E3');
INSERT INTO my_table VALUES ('F5');
INSERT INTO my_table VALUES ('G5');
INSERT INTO my_table VALUES ('F6');
INSERT INTO my_table VALUES ('G7');

Result:

5

---

UPDATE: Further to the comment below, you may want to consider using the ltrim() function:

The ltrim(X,Y) function returns a string formed by removing any and all characters that appear in Y from the left side of X.

Example:

SELECT COUNT(*) 
FROM   my_table 
WHERE  CAST(ltrim(id, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ') as int) <= 4;

Test case (adding to the above):

INSERT INTO my_table VALUES ('ABC1');
INSERT INTO my_table VALUES ('ZWY2');

New Result:

7

Answer 3

In MySQL that would be:

SELECT  COUNT(ID)
FROM    My_Table
WHERE   ID < 
        (
        SELECT  id
        FROM    (
                SELECT  ID
                FROM    My_Table
                WHERE   ID LIKE '%4'
                ORDER BY
                        ID
                LIMIT 1
                ) q
        UNION ALL
        SELECT  MAX(id)
        FROM    mytable
        LIMIT 1
        )