Post reply

Maximum ID with other columns

  • November 3, 2009 at 9:39 am

    #137052

    I'm trying to get the maximum id value (identity column) with other columns:

    select max(id), loannumber, sequence from paymenttransaction where loannumber = '0025187956'

    error:

    Column 'paymenttransaction.LoanNumber' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

  • November 3, 2009 at 9:46 am

    #1074364

    Try this

    with cte as (

    select id,loannumber, sequence,rank() over(partition by loannumber order by id desc) as rk

    from paymenttransaction

    where loannumber = '0025187956')

    select * from cte

    where rk=1

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537

  • November 3, 2009 at 10:04 am

    #1074383

    Thanks Mark!!

Viewing 3 posts - 1 through 2 (of 2 total)

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