Professional Web Applications Themes

get data from previous row - Microsoft SQL / MS SQL Server

Assuming, by 'previous' you meant the row with a lower id value, you can do: SELECT id, name, amount, COALESCE((SELECT TOP 1 t1.Amount FROM tbl t1 WHERE t1.Name = tbl.Name AND t1.Id < tbl.Id ORDER BY Id DESC), Amount) FROM tbl ; -- - Anith ( Please reply to newsgroups only )...

  1. #1

    Default Re: get data from previous row

    Assuming, by 'previous' you meant the row with a lower id value, you can do:

    SELECT id, name, amount,
    COALESCE((SELECT TOP 1 t1.Amount
    FROM tbl t1
    WHERE t1.Name = tbl.Name
    AND t1.Id < tbl.Id
    ORDER BY Id DESC), Amount)
    FROM tbl ;

    --
    - Anith
    ( Please reply to newsgroups only )


    Anith Guest

  2. #2

    Default Re: get data from previous row

    Essaie ca or Try This

    SELECT
    ID
    Name,
    Amount,
    COALESCE((SELECT TOP 1 Amount FROM tbl1 t2 WHERE t2.ID < t1.ID AND
    t2.Name = t1.NAme ORDER BY ID DESC),t1.Amount)
    FROM
    tbl1 t1


    "Derek Ruesch" <com> wrote in message news:<091301c36598$efbea930$gbl>... 
    Guy Guest

  3. #3

    Default Re: get data from previous row

    Tip: you might want to check if the query runs faster if you replace
    COALESCE with ISNULL.

    Gert-Jan


    Derek Ruesch wrote: 
    > value, you can do: [/ref]
    Gert-Jan Guest

  4. #4

    Default Re: get data from previous row

    What about

    select id, name, amount, (select amount from thetable where id = case when
    o.id = 1 then 1 else o.id - 1 end)
    from thetable o




    "Derek Ruesch" <com> wrote in message
    news:091301c36598$efbea930$gbl... 


    Wayne Guest

Similar Threads

  1. Replies: 3
    Last Post: August 27th, 07:26 PM
  2. Replies: 3
    Last Post: August 4th, 04:03 PM
  3. Replies: 2
    Last Post: August 3rd, 03:33 AM
  4. Repeat same data from previous fields?
    By Randy in forum Microsoft Access
    Replies: 0
    Last Post: June 28th, 02:06 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