Post reply

help filtering in a where

  • January 8, 2008 at 3:52 pm

    #180246

    Ok first off I"m a newb and just recently had to mess with SQL because I do consulting and ended up on the wrong end of the tech/funcional consultant spectrum durring this implementation... Now with that said HELP haha.

    Ok this is the deal I have a table that registers transactions, for the question lets say it has a part number and a date. Being that it is a transactional table i will have the same part number registerd for several transactions at different dates.

    What I want to do is make a select where I am only selecting the most recent transaction for a part. Lets say:

    Part date

    001 10/10/2007

    001 10/11/2007

    The only one I am intereste in seeing is the most recent one which in this case would be the 10/11/2007 one. To reiterate I want to be able to see ONLY the last time a transaction was done on that part.

    Thanks!

  • January 8, 2008 at 5:56 pm

    #765969

    SELECT Part, MAX(Date) AS Date

    FROM yourtable

    GROUP BY Part

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • January 8, 2008 at 10:34 pm

    #766016

    Jeff Moden (1/8/2008)

    SELECT Part, MAX(Date) AS Date

    FROM yourtable

    GROUP BY Part

    thanks bud, I had actually found it and thought I deleted the post. Now I'm glad I did not.

    I have a select statment that has many more fields when i do my group by I do

    part, date, etc, etc etc

    However when I do just part I get single part numbers, when I do part, date some of them show up twice and I am not sure why.

  • January 9, 2008 at 2:06 am

    #766050

    GROUP BY returns records that have been "summarized" and "grouped" in the order of fields you choose. When you add fields to the SELECT clause without a summary function, you are misled by the error messages to include them in the GROUP BY clause, although this often distorts the resultset from your target.

    The trick is to use a subquery, like:

    [Code]SELECT Part, Date, OrderNo, Qty, Price

    FROM Table1 a

    INNER JOIN

    (

    SELECT Part, MAX(Date) as Date

    FROM Table1 b

    GROUP BY Part, Date

    )

    ON a.Part = b.Part

    AND a.Date = b.Date[/Code]

    Regards,

    goodguy

    Experience is a bad teacher whose exams precede its lessons

  • January 9, 2008 at 6:21 am

    #766121

    thanks bud!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply