Post reply

Newbie Question: Help with select statement

  • December 9, 2003 at 10:37 am

    #101796

    I was just tossed into SQL with no experience and am needing some assistance with what may be easy for some of you. Thanks in advance!!!

    I have a database with one table that looks similar to this:

    ID Year CUM_GPA Class

    01 1998 2.56 FR

    01 2003 3.5 JR

    01 1983 1.9 FR

    01 2001 3.1 JR

    02 1999 2.66 FR

    02 2004 3.4 SR

    02 1993 1.0 FR

    02 2000 3.0 JR

    I need to select all the fields of each specific ID however I only want to get the row with the more current year. So my info would look like this.

    ID Year CUM_GPA Class

    01 2003 3.5 JR

    02 2004 3.4 SR

    The Year field is a CHAR.

    Thank you again in advance for any help.

  • December 9, 2003 at 12:26 pm

    #485389 

     

    Select * 

    From TableName A

    Where year = (Select MAX(year) From TableName B Where B.ID = A.ID)

  • December 9, 2003 at 2:25 pm

    #485430

    Thank you very much! It is almost what I need.

    I have approximately 2 million ID's, and I need the MAX(year) for each specific ID. The statement you suggested gave me all the correct information but it only grabbed the MAX(year).

    Am I making sense?

    Thanks so much!

  • December 9, 2003 at 2:53 pm

    #485434

    quote:

    Thank you very much! It is almost what I need.

    I have approximately 2 million ID's, and I need the MAX(year) for each specific ID. The statement you suggested gave me all the correct information but it only grabbed the MAX(year).

    Am I making sense?

    Thanks so much!

    mdaniel's query looks good to me!

    because you have a big table you may want to experiment a little and try several queries

    like:

    SELECT

    T1.ID, T1.Year, T1.CUM_GPA, T1.Class

    FROM

    TABLE T1

    INNER JOIN

    (

    SELECT ID, MAX(YEAR) AS YEAR

    FROM TABLE T2

    GROUP BY ID

    )T3

    ON T3.ID = T1.ID AND T1.YEAR = T3.YEAR

    because I don't have a representative data I can't tell what's better but both have the same query cost and then the use of the indexes will lead you one way or the other!!


    * Noel

  • December 10, 2003 at 1:22 am

    #485474

    Dammek, the reason mdaniel's query doesn't work is because your rows don't have a unique identifier. noeld's works great, though, because you can join on both the ID column and the maximum year. The unique key you want is actually:

    select ID, max(Year)

    From Table

    Group by ID

    since it's composite you should use a "sub" select in your FROM clause. Ideally each table should have a unique key, though.

    Signature is NULL

  • December 10, 2003 at 7:57 am

    #485545

    I'm sorry, but I can't see why mdaniel's query shouldn't work. I disagree with Calvin that it would require a unique key in this case, composite or otherwise.

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

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