sql query

Discussion in 'ASP General' started by dave, Feb 6, 2004.

  1. dave

    dave Guest

    hi
    Is it possible to write sql query to fetch records from
    specific record number?

    lets say i have one variable and it stores values such as
    1,2,3 or so...

    if variable contains, i want to fetch records from 1 to
    20.
    if it contains 2, then records should be from 21 to 40.

    for 3, records would be 41 to 60...

    what could be the sql for that?
    i dont wannna fetch whole table and then filtering
    through code..

    any help would be appreciated...
    cheers,
    dave
     
    dave, Feb 6, 2004
    #1
    1. Advertising

  2. How about this???

    CREATE PROCEDURE usp_TempProc
    @ContainKey INT=0
    AS
    BEGIN
    SET NOCOUNT ON

    DECLARE @FromKey INT
    DECLARE @ToKey INT

    IF @ContainKey = 0 Or @ContainKey IS NULL
    SELECT @ContainKey = 1

    SELECT @ToKey = @ContainKey * 20 -- ListCount = 20
    SELECT @FromKey = @ToKey - 19

    -- Fetch Records

    SELECT * FROM TableName WHERE [ContainKey] BETWEEN @FromKey TO @ToKey


    SET NOCOUNT OFF
    END
     
    권민수, Feb 6, 2004
    #2
    1. Advertising

  3. > SELECT * FROM TableName WHERE [ContainKey] BETWEEN @FromKey TO @ToKey

    This assumes that ContainKey represents a consecutive set of values, with no
    gaps. Not likely to stay that way in the real world.

    --
    Aaron Bertrand
    SQL Server MVP
    http://www.aspfaq.com/
     
    Aaron Bertrand [MVP], Feb 6, 2004
    #3
  4. dave

    Evertjan. Guest

    Aaron Bertrand [MVP] wrote on 06 feb 2004 in
    microsoft.public.inetserver.asp.general:
    >> SELECT * FROM TableName WHERE [ContainKey] BETWEEN @FromKey TO
    >> @ToKey

    >
    > This assumes that ContainKey represents a consecutive set of values,
    > with no gaps. Not likely to stay that way in the real world.
    >


    The whole concept of recordnumbers in a relational database is wrong.

    Unique numbering, which is something else than consecutive record
    numbering, must only be used to point to records inside the WHERE clause
    and to identify duplicate records.

    Records can have a consecutive numbers in a view,
    but those numbers are different in different views and in time.

    That is my view.

    --
    Evertjan.
    The Netherlands.
    (Please change the x'es to dots in my emailaddress)
     
    Evertjan., Feb 6, 2004
    #4
  5. dave

    Chris Barber Guest

    I presume that you want to do paging of the recordset:
    http://www.aspfaqs.com/webtech/062899-1.shtml

    Record numbering is unrealistic and in most circumstances impossible to
    maintain.

    You are *always* better off constructing a query with appropriate filtering
    that returns the exact data that you want or use the functionality above to
    ensure paging of that data to reduce the data displayed at any one time.

    Chris.

    "dave" <> wrote in message
    news:aa8801c3ec4e$cd491970$...
    hi
    Is it possible to write sql query to fetch records from
    specific record number?

    lets say i have one variable and it stores values such as
    1,2,3 or so...

    if variable contains, i want to fetch records from 1 to
    20.
    if it contains 2, then records should be from 21 to 40.

    for 3, records would be 41 to 60...

    what could be the sql for that?
    i dont wannna fetch whole table and then filtering
    through code..

    any help would be appreciated...
    cheers,
    dave
     
    Chris Barber, Feb 6, 2004
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Learner
    Replies:
    1
    Views:
    1,012
    Marina Levit [MVP]
    Jan 30, 2006
  2. Edward
    Replies:
    4
    Views:
    4,642
    William \(Bill\) Vaughn
    Apr 10, 2006
  3. Anonymous
    Replies:
    0
    Views:
    1,532
    Anonymous
    Oct 13, 2005
  4. David Gordon

    xpath query query

    David Gordon, May 18, 2005, in forum: XML
    Replies:
    2
    Views:
    819
    David Gordon
    May 18, 2005
  5. ecoolone
    Replies:
    0
    Views:
    790
    ecoolone
    Jan 3, 2008
Loading...

Share This Page