Results 1 to 11 of 11
  1. #1
    Join Date
    Apr 2008
    Posts
    20

    Unanswered: limit and found_Rows Equivalent functions in sybase

    Hi,
    I am newbie to sybase. Can some one tell me Sybase equivalent functions for the following MYSQL functions

    limit
    found_rows
    SQL_CALC_FOUND_ROWS

  2. #2
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    limit: set rowcount X, where X is the number of rows you want the limit set to.
    found rows: select @@rowcount. returns the number of rows affected in the last query.

    I don't know what SQL_CALC_FOUND_ROWS does, so I can;t help you with that one.
    I'm not crazy, I'm an aeroplane!

  3. #3
    Join Date
    Apr 2008
    Posts
    20
    thanks, martijnvs.

    SQL_CALC_FOUND_ROWS does the following functionaliy :-

    A SELECT statement may include a LIMIT clause to restrict the number of rows the server returns to the client. In some cases, it is desirable to know how many rows the statement would have returned without the LIMIT, but without running the statement again. To obtain this row count, include a SQL_CALC_FOUND_ROWS option in the SELECT statement, and then invoke FOUND_ROWS() afterward:

    mysql> SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name
    -> WHERE id > 100 LIMIT 10;
    mysql> SELECT FOUND_ROWS();

    The second SELECT returns a number indicating how many rows the first SELECT would have returned had it been written without the LIMIT clause.

    In the absence of the SQL_CALC_FOUND_ROWS option in the most recent SELECT statement, FOUND_ROWS() returns the number of rows in the result set returned by that statement.


    is there any way in sybase with same functionality what SQL_CALC_FOUND_ROWS do..

  4. #4
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    Hmm, I do not know of any function that does that.
    Supposed you have this query: select id, name from mytable:
    Code:
    set rowcount 200
    select id, name from mytable
    set rowcount 0
    will give 200 records (limit)
    Code:
    set rowcount 200
    select id, name from mytable
    select @@rowcount)
    set rowcount 0
    will give 200 records, plus the number 200, for the 200 records returned.

    If you want to know how many records would have returned without the set rowcount-command, you could use this one:
    Code:
    select count(id) from mytable
    This one only returnes the number of rows of that query. It is calculated on the server, so it should save you some time (the time it would take to send the resultset and display it on your client).
    I'm not crazy, I'm an aeroplane!

  5. #5
    Join Date
    Apr 2008
    Posts
    20
    Martijnvs, thank for the info..

    but actually now i am doing porting. so i have to write a single query to limit the no of records. by using rowcount i have to send more than 1 query from my application. is there any smart way to do in a single query??

  6. #6
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    Quote Originally Posted by mohanrao
    Martijnvs, thank for the info..

    but actually now i am doing porting. so i have to write a single query to limit the no of records. by using rowcount i have to send more than 1 query from my application. is there any smart way to do in a single query??
    Why would you want to limit your resultset with something like 'limit'? If you want a specific, limited set of records, you can use a where-clause, but you can't specify an exact number of records as far as i know.

    Can you post a sample of a query you want to port?
    I'm not crazy, I'm an aeroplane!

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Martijnvs
    Why would you want to limit your resultset with something like 'limit'?
    for the same reason that sybase developers use TOP

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Apr 2008
    Posts
    20
    select day(servlet_start_time) as DayOfMonth
    from
    sla
    group by
    day(servlet_start_time)
    order by
    $orderby$
    limit
    $startRow$, $numRows$

    this is the query ...i am using this query for pagination..i am planning to use pagination support by database

  9. #9
    Join Date
    Apr 2008
    Posts
    20
    r937 , top is rocking but..if i want to get the rows from 15 to 20..like this..is there any some function..to do like this..i need this for paging

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i would just return all 31 days
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    Dude, Google "Sybase LIMIT" or "SQL SERVER LIMIT" and you'll see exactly how to do the offset in T-SQL. That would've taken far less time than posting this thread.
    Thanks,

    Matt

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •