Professional Web Applications Themes

previous row question - Microsoft SQL / MS SQL Server

This has to do with my previous question with a twist: If we are looking at the first row for someone, I want a NULL value for the Amount_Previous column. I have the following table: tbl1 ID (int-identity) Name (varchar (30)) Amount (money) 1 Dave 40 2 Dave 50 3 Nate 60 4 Nate 70 5 Dave 80 6 Dave 40 7 Nate 50 I want to write a query that will return the following results: ID Name Amount Previous_Amt 1 Dave 40 NULL 2 Dave 50 40 3 Nate 60 NULL 4 Nate 70 60 5 Dave 80 50 ...

  1. #1

    Default previous row question

    This has to do with my previous question with a twist: If
    we are looking at the first row for someone, I want a NULL
    value for the Amount_Previous column.

    I have the following table:

    tbl1

    ID (int-identity) Name (varchar (30)) Amount (money)
    1 Dave 40
    2 Dave 50
    3 Nate 60
    4 Nate 70
    5 Dave 80
    6 Dave 40
    7 Nate 50

    I want to write a query that will return the following
    results:
    ID Name Amount Previous_Amt
    1 Dave 40 NULL
    2 Dave 50 40
    3 Nate 60 NULL
    4 Nate 70 60
    5 Dave 80 50
    6 Dave 40 80
    7 Nate 50 70

    The Previous_Amt for Dave on the first record is NULL
    because Dave didn't have a record before this one. The
    second record for Dave is 40 because the first record's
    amount was 40. This works the same for Nate.

    I would greatly appreciate any help. Thanks.

    Derek Ruesch

    Derek Guest

  2. #2

    Default Re: previous row question

    "Derek Ruesch" <com> wrote in message
    news:083a01c365af$45be2fc0$gbl... 

    SELECT T1.id, T1.name, T1.amount, T2.amount AS previous_amt
    FROM tbl1 AS T1
    LEFT OUTER JOIN
    tbl1 AS T2
    ON T1.name = T2.name AND
    T2.id < T1.id AND
    NOT EXISTS (SELECT *
    FROM tbl1 AS T3
    WHERE T3.name = T1.name AND
    T3.id > T2.id AND
    T3.id < T1.id)

    Regards,
    jag


    John Guest

  3. #3

    Default Re: previous row question

    Do:

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

    --
    - Anith
    ( Please reply to newsgroups only )


    Anith Guest

  4. #4

    Default Re: previous row question

    Try:
    (untested)
    select id, name,(select amount from table1 where id =
    (select max(id) from table1 b where b.id < a.id and b.name = a.name))
    'Prev_amt'
    from table1 a

    --
    -Vishal
    "Derek Ruesch" <com> wrote in message
    news:083a01c365af$45be2fc0$gbl... 


    Vishal Guest

Similar Threads

  1. Next/Previous help
    By Wants2learn in forum Coldfusion - Getting Started
    Replies: 12
    Last Post: August 3rd, 01:17 PM
  2. Help with Previous Value
    By galeemma in forum Dreamweaver AppDev
    Replies: 11
    Last Post: July 11th, 05:24 PM
  3. Thanks for previous responses!
    By Falcon in forum FileMaker
    Replies: 4
    Last Post: March 4th, 01:10 PM
  4. Next and Previous record
    By Rick in forum PHP Development
    Replies: 5
    Last Post: November 9th, 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