Professional Web Applications Themes

aggregate query help - Microsoft SQL / MS SQL Server

"Sean Grimes" <com> wrote in message news:0e0d01c35b67$2756b9a0$gbl...  Assuming table is T, SELECT T1.* FROM T AS T1 LEFT OUTER JOIN T AS T2 ON T1.KeyColumn1 = T2.KeyColumn1 AND T1.KeyColumn2 = T2.KeyColumn2 AND T2.KeyInstance > T1.KeyInstance WHERE T2.KeyInstance IS NULL Regards, jag...

  1. #1

    Default Re: aggregate query help

    "Sean Grimes" <com> wrote in message
    news:0e0d01c35b67$2756b9a0$gbl... 

    Assuming table is T,

    SELECT T1.*
    FROM T AS T1
    LEFT OUTER JOIN
    T AS T2
    ON T1.KeyColumn1 = T2.KeyColumn1 AND
    T1.KeyColumn2 = T2.KeyColumn2 AND
    T2.KeyInstance > T1.KeyInstance
    WHERE T2.KeyInstance IS NULL

    Regards,
    jag


    John Guest

  2. #2

    Default Re: aggregate query help

    select a.* from table1 a inner join
    (select max(keyinstance) keyinstance, keycolumn1,keycolumn2
    frrom table1 group by keycolumn1,keycolumn2) b
    on a.keyinstance = b.keyinstance and a.keycolumn1 =b.keycolumn1 and
    a.keycolumn2 =b.keycolumn2

    --
    -Vishal

    "Sean Grimes" <com> wrote in message
    news:0e0d01c35b67$2756b9a0$gbl... 


    Vishal Guest

  3. #3

    Default Re: aggregate query help

    Here are two possibilities, both untested:

    SELECT S1.*
    FROM Sometable AS S1
    JOIN
    (SELECT keycolumn1, keycolumn2,
    MAX(keyinstance) AS keyinstance
    FROM Sometable
    GROUP BY keycolumn1, keycolumn2) AS S2
    ON S1.keycolumn1 = S2.keycolumn1
    AND S1.keycolumn2 = S2.keycolumn2
    AND S1.keyinstance = S2.keyinstance

    SELECT *
    FROM Sometable AS S
    WHERE keyinstance =
    (SELECT MAX(keyinstance)
    FROM Sometable
    WHERE keycolumn1 = S.keycolumn1
    AND keycolumn2 = S.keycolumn2)

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --



    David Guest

Similar Threads

  1. aggregate functions -- does value exist in group
    By jezaustin@gmail.com in forum MySQL
    Replies: 3
    Last Post: August 23rd, 04:46 PM
  2. Aggregate Function Nightmare
    By Henweigh99 in forum Macromedia ColdFusion
    Replies: 15
    Last Post: July 14th, 02:54 AM
  3. Aggregate formfields
    By Pascal_Kamperman@adobeforums.com in forum Adobe Acrobat Windows
    Replies: 1
    Last Post: April 26th, 08:27 AM
  4. Aggregate Bitwise OR Function
    By Mark in forum IBM DB2
    Replies: 3
    Last Post: September 25th, 06:53 PM
  5. Adding aggregate functions
    By Dejan in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 22nd, 02:10 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