0

I've a query that pulls records based on the search parameter, and I want query to return total records as well as paginate based on start and end, do I've to run two queries or is there more intuitive way to do in a single query.

SELECT * FROM page WHERE article_id = ? AND content like '%@%' //replacing @ with keyword

Edit: Looking for Standard SQL (using WebSQL actually)

  • Will depend largely on the DBMS you're using – David Faber Jan 29 '15 at 16:59
  • I'm using websql so pretty much wanna stay inside standard sql –  Jan 29 '15 at 17:01
  • There isn't a "standard" way of paginating results – David Faber Jan 29 '15 at 17:20
  • I'm pretty sure SQLLite is the only database server used by anyone who implemented WebSQL. SQLLite does have a LIMIT and OFFSET that you can use. There's any number of threads here on SO that have what your looking for: http://stackoverflow.com/questions/14468586/efficient-paging-in-sqlite-with-millions-of-records – Mike Jan 29 '15 at 17:56

2 Answers2

0

This query will return for each row the total of records and the actual page (considering there is 5 rows per page)

select *, count(*) as total, (seq - MOD(seq, 5)) / 5
from (select id, row_number() over(order by id) as seq
      from page) a
join page b on b.id = a.id;

Note that this query will works only for Oracle since you did not specify your DBMS I decided by myself so you might have to edit.

Jean-François Savard
  • 20,626
  • 7
  • 49
  • 76
  • actually i did mention some comments on it, looking for standard sql, editing my question, can you please help me adopt this one http://stackoverflow.com/questions/19378892/get-total-count-of-rows-in-pagination-query –  Jan 29 '15 at 17:04
  • @user3610227: that **is** "standard SQL" (except for the `mod()` maybe) –  Jan 29 '15 at 17:14
  • I can't, it has functions which I don't have a clue on, for instance `over` and `row_number`, I'm looking for a websql based solution, possible please, error `could not prepare statement (1 near "(": syntax error)"` –  Jan 30 '15 at 17:00
-1

Try to use a subquery

count * from table where (select .....)
gunr2171
  • 16,104
  • 25
  • 61
  • 88
Khouadja
  • 39
  • 1
  • 7
  • Thanks, I found a link but unable to understand so far, can you please help in that perspective http://stackoverflow.com/questions/19378892/get-total-count-of-rows-in-pagination-query –  Jan 29 '15 at 17:00