CODEWITHSUNDEEP
sqldatabasedatabase-designsqlite
T.T.T.
T.T.T.

Reputation: 34603

How do I find the count of columns before the first occurrence of LIKE?

Sorted on column Y

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

User has option to enter wild card search, i.e. - "%5"

I would like to return to the user page 2 (as it has the first occurrence of something followed by 5.) OR find out how many rows there are before the column containing F5

(SQLite with C API)

Upvotes: 1

Views: 112

Answers (2)

Fosco
Fosco

Reputation: 38526

Assuming MSSQL, here's a roundabout way that kind of steps through the logic.. let me know if it doesn't help:

declare @perPage int
declare @searchString varchar(20)
declare @countBefore int
declare @firstMatch varchar(20)
declare @resultPage int

set @perPage = 3
set @searchString = '%5'

select @firstMatch = (select top 1 y from myTable where y like @searchString order by y)
select @countBefore = (select count(*) from myTable where y < @firstMatch)
select @resultPage = (@countBefore / @perPage) + 1

Upvotes: 1

Quassnoi
Quassnoi

Reputation: 425713

Assuming MySQL, resultset ordered by X and X is unique:

SELECT  COUNT(*)
FROM    mytable
WHERE   X <
        (
        SELECT  X
        FROM    mytable
        WHERE   y LIKE '%5'
        ORDER BY
                X
        LIMIT 1
        )

Upvotes: 1

Related Questions