Professional Web Applications Themes

to get the nth record meeting the query requirement - Microsoft SQL / MS SQL Server

In SQL server, if I want to retrieve the first record which meets the query, I can use 'select top 1'. However if I want to query the nth record, is there a easy way to do it? Thanks. USE pubs SELECT TOP 1 pub_id FROM titles WHERE type ='business' ORDER BY pub_id...

  1. #1

    Default to get the nth record meeting the query requirement

    In SQL server, if I want to retrieve the first record which meets the query,
    I can use 'select top 1'. However if I want to query the nth record, is
    there a easy way to do it?
    Thanks.

    USE pubs
    SELECT TOP 1 pub_id
    FROM titles
    WHERE type ='business'
    ORDER BY pub_id



    Michelle Guest

  2. #2

    Default Re: to get the nth record meeting the query requirement

    Would something like this work? replace n with the required record.

    select TOP 1 pub_id from (
    > SELECT TOP n pub_id
    > FROM titles
    > WHERE type ='business'
    > ORDER BY pub_id
    )
    ORDER BY pub_id desc

    Cheers
    Stu


    Stu Guest

  3. #3

    Default Re: to get the nth record meeting the query requirement

    If you meant the nth largest/smallest value in the dataset, you can try :

    SELECT pub_id
    FROM titles
    WHERE type = 'business'
    AND (SELECT COUNT(t1.pub_id)
    FROM titles t1
    WHERE t1.type = titles.type
    AND t1.pubid <= titles.pubid) = n

    If you need to handles ties, you have to use COUNT(DISTINCT pub_id) in your
    subquery.

    --
    - Anith
    ( Please reply to newsgroups only )


    Anith Sen Guest

  4. #4

    Default Re: to get the nth record meeting the query requirement

    Try this:

    USE pubs
    declare x int
    set x=7 --get 7th record on the basis of order of pub_id
    select rank, pub_id from
    (SELECT (select count(distinct pub_id) from titles where pub_id <= a.pub_id)
    rank, pub_id
    FROM titles a
    WHERE type ='business') X
    where rank=x


    -Vishal

    "Michelle" <Michelle> wrote in message
    news:ONo1AJxPDHA.1336TK2MSFTNGP11.phx.gbl...
    > In SQL server, if I want to retrieve the first record which meets the
    query,
    > I can use 'select top 1'. However if I want to query the nth record, is
    > there a easy way to do it?
    > Thanks.
    >
    > USE pubs
    > SELECT TOP 1 pub_id
    > FROM titles
    > WHERE type ='business'
    > ORDER BY pub_id
    >
    >
    >

    Vishal Parkar Guest

  5. #5

    Default Re: to get the nth record meeting the query requirement

    USE pubs
    SELECT TOP 1 pub_id
    FROM titles
    WHERE type ='business'
    ORDER BY pub_id
    DESC --changed the clause to DESC to get the last record.

    -Vishal


    "Michelle" <Michelle> wrote in message
    news:#evxGMxPDHA.1336TK2MSFTNGP11.phx.gbl...
    > or how can I retrieve the last record meeting the query? Thanks for your
    > help.
    >
    > "Michelle" <Michelle> wrote in message
    > news:ONo1AJxPDHA.1336TK2MSFTNGP11.phx.gbl...
    > > In SQL server, if I want to retrieve the first record which meets the
    > query,
    > > I can use 'select top 1'. However if I want to query the nth record, is
    > > there a easy way to do it?
    > > Thanks.
    > >
    > > USE pubs
    > > SELECT TOP 1 pub_id
    > > FROM titles
    > > WHERE type ='business'
    > > ORDER BY pub_id
    > >
    > >
    > >
    >
    >

    Vishal Parkar Guest

Similar Threads

  1. Replies: 2
    Last Post: November 8th, 10:45 AM
  2. [MEETING] CORE Meeting Oct. 25th
    By Daniel Berger in forum Ruby
    Replies: 1
    Last Post: October 21st, 05:37 AM
  3. Last record of query
    By Jesper Nielsen in forum PHP Development
    Replies: 2
    Last Post: October 13th, 08:51 AM
  4. [MEETING] CORE Meeting August 9th
    By Daniel Berger in forum Ruby
    Replies: 0
    Last Post: August 1st, 04:54 PM
  5. Adding a new record when the record source is a query.
    By Andy in forum Microsoft Access
    Replies: 0
    Last Post: July 29th, 01:41 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