Results 1 to 11 of 11
  1. #1
    Join Date
    Nov 2011
    Posts
    24

    Unanswered: Max id if multiple records exist help please!!

    Hi,

    i am having a hard time figuring out how to go about this.

    so basically IF multiple records with the same start date exist, i have to select the one with the max(note_id)

    Example
    note_id/start_date
    88888/08-01-2001
    98788/08-01-2001 <===
    55555/08-01-2001
    54856/08-01-2001

    in the example above, i would need to pick the one with the arrow
    note_id-98788 and start_date-08-01-2001

  2. #2
    Join Date
    Apr 2004
    Location
    Arizona
    Posts
    75
    Can you post the SQL you are using because MAX should work? You may have some Group By that is causing the issue.

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    SELECT * FROM yourTable o
    WHERE EXISTS (
    SELECT * FROM youtTable i
    GROUP BY start_date
    HAVING o.note_Id = MAX(i.note_Id)
    )
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Nov 2011
    Posts
    24
    Quote Originally Posted by Brett Kaiser View Post
    SELECT * FROM yourTable o
    WHERE EXISTS (
    SELECT * FROM youtTable i
    GROUP BY start_date
    HAVING o.note_Id = MAX(i.note_Id)
    )
    Thanks Brett

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You should be using a CTE for this. Much more flexible.
    Code:
    with OrderedData as
    	(select	*,
    		ROW_NUMBER() over (partition by start_date order by note_id desc) as RowNum
    	from	yourtable)
    select	*
    from	OrderedData
    where	RowNum = 1
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    What do you mean by More flexible?

    If you mean non-portable, den I'm wit cha
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by Brett Kaiser View Post
    If you mean non-portable, den I'm wit cha
    Why would the CTE (or windowing functions) be non-portable?
    Blindman's example is 100% ANSI SQL and would work without changes on a wide range of DBMS.

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    DB2..Oracle?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by Brett Kaiser View Post
    DB2..Oracle?
    PostgreSQL, Firebird 3.0, Sybase, Teradata, Greenplum

    That syntax was working for Oracle about 15 years ago

  11. #11
    Join Date
    Apr 2004
    Location
    Arizona
    Posts
    75
    Quote Originally Posted by Brett Kaiser View Post
    SELECT * FROM yourTable o
    WHERE EXISTS (
    SELECT * FROM youtTable i
    GROUP BY start_date
    HAVING o.note_Id = MAX(i.note_Id)
    )
    There is why. You are doing a sub-select with a group by start_date.

    Try not using the subselect like...

    SELECT MAX(i.note_Id),* FROM yourTable
    GROUP BY start_date

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •