Professional Web Applications Themes

MSsql's alternative for MySQL's LIMIT command - Microsoft SQL / MS SQL Server

what does limit do? if it limits the result set use top if you can, else use "set rowcount n" Mark Baekdal dbghost.com >-----Original Message----- >we are migrating a huge application from mySQL to MSSQL. >we are facing a huge problem because we are depending >heavily on mySQL's LIMIT command in our sql select >statements. nothing that we tried worked and we need a >generic solution possibly a stored procedure unless there >is a known solution to the problem. Any ideas/solutions? >Thank you. >. >...

  1. #1

    Default MSsql's alternative for MySQL's LIMIT command

    what does limit do?

    if it limits the result set use top if you can, else
    use "set rowcount n"

    Mark Baekdal
    dbghost.com
    >-----Original Message-----
    >we are migrating a huge application from mySQL to MSSQL.
    >we are facing a huge problem because we are depending
    >heavily on mySQL's LIMIT command in our sql select
    >statements. nothing that we tried worked and we need a
    >generic solution possibly a stored procedure unless there
    >is a known solution to the problem. Any ideas/solutions?
    >Thank you.
    >.
    >
    mark baekdal Guest

  2. #2

    Default Re: MSsql's alternative for MySQL's LIMIT command

    Use SET ROWCOUNT or TOP

    Example:

    SET ROWCOUNT 100

    or

    SELECT TOP 100 * FROM Sometable ORDER BY somecolumn

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --

    "Amr Al Jarhi" <amraljarhi> wrote in message
    news:0c6c01c3452d$c496ae00$a101280aphx.gbl...
    > we are migrating a huge application from mySQL to MSSQL.
    > we are facing a huge problem because we are depending
    > heavily on mySQL's LIMIT command in our sql select
    > statements. nothing that we tried worked and we need a
    > generic solution possibly a stored procedure unless there
    > is a known solution to the problem. Any ideas/solutions?
    > Thank you.

    David Portas Guest

  3. #3

    Default Re: MSsql's alternative for MySQL's LIMIT command

    SELECT
    TOP 10 *
    FROM
    T1

    or

    SET ROWCOUNT 10
    SELECT
    *
    FROM
    T1


    --
    ==
    fabriZio

    "Amr Al Jarhi" <amraljarhi> ha scritto nel messaggio
    news:0c6c01c3452d$c496ae00$a101280aphx.gbl...
    > we are migrating a huge application from mySQL to MSSQL.
    > we are facing a huge problem because we are depending
    > heavily on mySQL's LIMIT command in our sql select
    > statements. nothing that we tried worked and we need a
    > generic solution possibly a stored procedure unless there
    > is a known solution to the problem. Any ideas/solutions?
    > Thank you.

    fabriZio Guest

  4. #4

    Default Re: MSsql's alternative for MySQL's LIMIT command

    the LIMIT function is the same like the TOP function but
    takes an extra parameter that specifies an offset, from
    which the count should start.
    for ex:
    select * from tablename LIMIT 10,20
    this will return 10 records (at most) but will skip 20
    records first before adding the 10.
    i cannot do that with the TOP statement because it starts
    adding from the first record by default.
    Is this possible with MSSQl?
    Thanks all for your suggestions.

    Guest

  5. #5

    Default Re: MSsql's alternative for MySQL's LIMIT command

    As rows has no order in a relational database, your viewpoint has to be the resultset, not the
    table. Something like

    "First" 5
    SELECT TOP 5 au_lname, au_fname, au_id FROM authors
    ORDER BY au_lname ASC

    And "next" 5
    SELECT TOP 5 au_lname, au_fname, au_id FROM authors
    WHERE au_id NOT IN(SELECT TOP 5 au_id FROM authors
    ORDER BY au_lname ASC)
    ORDER BY au_lname ASC

    Watch out if there's a tie at last place. You might want to include the PK in the ordering and
    use EXISTS instead of IN.
    --
    Tibor Karaszi, SQL Server MVP
    Archive at: [url]http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver[/url]


    <amraljarhi> wrote in message news:678801c346c3$8a32d4e0$7d02280aphx.gbl...
    > the LIMIT function is the same like the TOP function but
    > takes an extra parameter that specifies an offset, from
    > which the count should start.
    > for ex:
    > select * from tablename LIMIT 10,20
    > this will return 10 records (at most) but will skip 20
    > records first before adding the 10.
    > i cannot do that with the TOP statement because it starts
    > adding from the first record by default.
    > Is this possible with MSSQl?
    > Thanks all for your suggestions.
    >

    Tibor Karaszi Guest

  6. #6

    Default Re: MSsql's alternative for MySQL's LIMIT command

    I usually page with something like this:


    SET NOCOUNT ON
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED --if dirty reads are
    ok

    DECLARE
    offset int,
    pagesize int,
    maxrows int

    -- example
    SET offset = 10
    SET pagesize = 10
    SET maxrows = (offset + pagesize -1)

    DECLARE tmp TABLE
    (
    RowNo INT IDENTITY(1,1) PRIMARY KEY,
    id int
    )
    SET ROWCOUNT maxrows

    INSERT INTO tmp (id)
    SELECT id FROM TableContent ORDER BY creationdate

    SELECT c.id, title, content
    FROM tmp tmp
    JOIN TableContent c ON c.id = tmp.id
    WHERE tmp.RowNo BETWEEN offset AND (offset + pagesize - 1)


    This, of course, looks insane compared to the handy LIMIT statement
    but actually does not perform as bad as it looks.

    Regards

    DC


    "Amr Al Jarhi" <amraljarhi> wrote in message news:<0c6c01c3452d$c496ae00$a101280aphx.gbl>...
    > we are migrating a huge application from mySQL to MSSQL.
    > we are facing a huge problem because we are depending
    > heavily on mySQL's LIMIT command in our sql select
    > statements. nothing that we tried worked and we need a
    > generic solution possibly a stored procedure unless there
    > is a known solution to the problem. Any ideas/solutions?
    > Thank you.
    DC Guest

Similar Threads

  1. Whoops -- character limit per line in Mysql or dodgy debugging on my part?
    By speralta@progressivetrail.org in forum MySQL
    Replies: 3
    Last Post: July 18th, 04:05 AM
  2. Replies: 6
    Last Post: March 8th, 11:47 AM
  3. IF EXISTS command in Access and MySQL
    By Laphan in forum ASP Database
    Replies: 0
    Last Post: June 5th, 08:49 PM
  4. The network BIOS command limit has been reached??
    By Chris in forum ASP Components
    Replies: 0
    Last Post: September 4th, 03:59 AM
  5. Replies: 3
    Last Post: January 8th, 09:38 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139