Professional Web Applications Themes

Need some help on a SELECT statement - Microsoft SQL / MS SQL Server

Hello All, I'd like to know if there is someone out there who could provide me with some help on T-SQL. Let's say I have the following data in an IBM DB2 table: ---------------------------------------- product_code effective_date price ---------------------------------------- AA 06-01-2003 1.0 AA 06-02-2003 1.1 AA 06-03-2003 1.2 BB 06-01-2003 2.0 BB 06-02-2003 2.1 CC 07-01-2003 3.0 ---------------------------------------- How would I write the SELECT statement that will give me the latest price for each product_code in the above example? In other words, the query will produce this resultset: ---------------------------------------- product_code effective_date price ---------------------------------------- AA 06-03-2003 1.2 BB 06-02-2003 2.1 CC 07-01-2003 ...

  1. #1

    Default Need some help on a SELECT statement

    Hello All,

    I'd like to know if there is someone out there who could provide me
    with some help on T-SQL.

    Let's say I have the following data in an IBM DB2 table:

    ----------------------------------------
    product_code effective_date price
    ----------------------------------------
    AA 06-01-2003 1.0
    AA 06-02-2003 1.1
    AA 06-03-2003 1.2
    BB 06-01-2003 2.0
    BB 06-02-2003 2.1
    CC 07-01-2003 3.0
    ----------------------------------------

    How would I write the SELECT statement that will give me the latest
    price for each product_code in the above example? In other words,
    the query will produce this resultset:

    ----------------------------------------
    product_code effective_date price
    ----------------------------------------
    AA 06-03-2003 1.2
    BB 06-02-2003 2.1
    CC 07-01-2003 3.0
    ----------------------------------------

    Just wanted to point it out that in my production table, I could have
    hundreds and thousands of records per product_code. So I am also
    looking for the most efficient way to accomplish this.

    Any help I can get is greatly appreciated. Please reply to me directly
    if possible.

    Thanks in advance!

    Sydney Luu
    com
    Sydney Guest

  2. #2

    Default Re: Need some help on a SELECT statement

    You will need a derived table:

    Select t.product_code, t.effective_date, t.price
    FROM tablename t INNER JOIN (
    Select product_code, max(effective_date) as maxdate
    FROM tablename) q
    ON t.product_code = q.product_code
    and t.effective_date = q.maxdate

    HTH,
    Bob Barrows


    Sydney Luu wrote: 


    Bob Guest

  3. #3

    Default Re: Need some help on a SELECT statement

    TRY:

    SELECT A.* FROM TABLE A INNER JOIN
    (SELECT PRODUCT_CODE, MAX(EFFECTIVE_dATE) EFFECTIVE_dATE FROM TABLE GROUP BY
    PRODUCT_CODE) b
    ON A.PRODUCT_CODE=B.PRODUCT_CODE AND A.EFFECTIVE_DATE=B.EFFECTIVE_DATE


    --
    -Vishal
    "Sydney Luu" <com> wrote in message
    news:google.com... 


    Vishal Guest

  4. #4

    Default Re: Need some help on a SELECT statement

    Just like in db2 :

    select a.product_code , a.effective_date, a.price
    from yourtable a
    inner join
    (select product_code , max(effective_date) as effective_date
    from from yourtable
    group by product_code ) b
    on a.product_code = b.product_code
    and a.effective_date = b.effective_date

    jobi
    "Sydney Luu" <com> wrote in message
    news:google.com... 


    jobi Guest

  5. #5

    Default Re: Need some help on a SELECT statement

    I forgot the GROUP BY clause in the derived table. See below:

    Bob Barrows wrote: 
    GROUP BY product_code) q [/ref]


    Bob Guest

  6. #6

    Default Re: Need some help on a SELECT statement

    Just wanted to say thank you all for your help and the quick
    response! I got my query to work. Thanks again!

    "Bob Barrows" <com> wrote in message news:<phx.gbl>... 
    > GROUP BY product_code) q [/ref][/ref]
    Sydney Guest

Similar Threads

  1. sql select case statement
    By Anderson11983 in forum Coldfusion Database Access
    Replies: 1
    Last Post: May 9th, 01:50 PM
  2. SP with Select statement
    By Gerald in forum ASP Database
    Replies: 3
    Last Post: November 27th, 03:38 PM
  3. help with SELECT statement
    By Chris Hohmann in forum ASP
    Replies: 2
    Last Post: August 19th, 05:10 PM
  4. SELECT statement
    By Simon in forum Microsoft SQL / MS SQL Server
    Replies: 23
    Last Post: August 18th, 01:12 PM
  5. Select Statement Help Please
    By Bob in forum Microsoft SQL / MS SQL Server
    Replies: 4
    Last Post: July 14th, 02: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