Professional Web Applications Themes

How to retrieve a sepecific row from a query? - Microsoft SQL / MS SQL Server

Hi, I have a query like the following: SELECT * FROM Book WHERE bookID<=266 This query returns me 189 rows. I know that TOP n can help me to get top n rows. But I just want a specific row (for example: 8th row) from this query result without any other criteria in WHERE clause. In another word, I like to get a row using some sort of index in the query result. Would you please give me some help? Thank you hb...

  1. #1

    Default How to retrieve a sepecific row from a query?

    Hi,

    I have a query like the following:
    SELECT * FROM Book WHERE bookID<=266

    This query returns me 189 rows. I know that TOP n
    can help me to get top n rows. But I just want a specific
    row (for example: 8th row) from this query result without
    any other criteria in WHERE clause. In another word,
    I like to get a row using some sort of index in the query
    result.

    Would you please give me some help?

    Thank you

    hb


    hb Guest

  2. #2

    Default Re: How to retrieve a sepecific row from a query?

    Hongbo,

    There is no default ordering in the way data is stored and so it mainly
    depends on the expression that you supply.See this example:

    --To get the TOP 8 rows ordered by book_id
    SELECT TOP 8 *
    FROM dbo.Book
    ORDER BY book_id

    --To get eighth row alone of the previous resultset
    SELECT TOP 1 *
    FROM dbo.Book
    WHERE book_id > ALL (SELECT TOP 7 book_id
    FROM dbo.Book ORDER BY book_id)
    ORDER BY book_id


    --
    Dinesh.
    SQL Server FAQ at
    http://www.tkdinesh.com

    "hb" <com> wrote in message
    news:%phx.gbl... 


    Dinesh.T.K Guest

  3. #3

    Default I found a solution

    Hi, Sampangi and Dinesh,

    Thank you for your help.

    I feel that I didn't make my question clear. Actually I need 1 row from the
    same query at a time
    and different time I need to choose a different row. But I found a solution.

    Here you are what I did:
    ===
    CREATE FUNCTION [dbo].[GetAccountElectr] (accountID int, type int, n
    int)
    RETURNS int AS
    BEGIN
    --type=1: usagekwh;2:demandkw;3:onPeak;4:offPeak;5:semiPeak
    --n=1~12:january~december
    --data covers the most recent 12 months
    DECLARE use int

    DECLARE e_cur CURSOR STATIC FOR
    SELECT TOP 12
    CASE type
    WHEN 1 THEN usagekwh
    WHEN 2 THEN demandkw
    WHEN 3 THEN onPeakkwh
    WHEN 4 THEN offPeakkwh
    WHEN 5 THEN semiPeakkwh
    ELSE usagekwh
    END
    FROM Electr WHERE accountID=accountID ORDER BY startDate DESC

    OPEN e_cur
    FETCH ABSOLUTE n FROM e_cur INTO use
    CLOSE e_cur
    DEALLOCATE e_cur

    IF use IS NULL
    BEGIN
    SET use=0
    END
    RETURN use
    END
    ===



    hb Guest

Similar Threads

  1. Query to retrieve price
    By design in progress in forum Coldfusion Database Access
    Replies: 2
    Last Post: May 16th, 01:14 AM
  2. Query of Queries on query New type query
    By david_h in forum Coldfusion Database Access
    Replies: 2
    Last Post: May 6th, 08:55 PM
  3. Retrieve URL from DB
    By coryd218 in forum Coldfusion Database Access
    Replies: 2
    Last Post: May 6th, 02:38 PM
  4. retrieve *.jpg
    By ma in forum Microsoft Access
    Replies: 0
    Last Post: August 4th, 04:45 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