Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2007
    Location
    Plzeň, Czech Republic
    Posts
    10

    Question Unanswered: Last Aggregate function

    Hello, I need to use last() aggregate function in MS SQL Server 2005 but it is not built in.

    How to replace this functionality?

    Thanks.
    Last edited by jsiii; 03-09-07 at 06:42.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    You can get this using row_number with an OVER clause in 2005. Check it out in BoL and see how you get on. HINT - you will need to order descending.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Mar 2007
    Location
    Plzeň, Czech Republic
    Posts
    10
    Thanks for the quick response. I however am not too sure how to do it.

    Let's say I have a query like this:

    Code:
    select 
        last(firstname), 
        lastname 
    
    from 
        users
    
    group by
        lastname
    but the last() function does not work, so how to do it using your method?

    I am quite new to the SQL so sorry for asking obvious things maybe :-)

  4. #4
    Join Date
    Mar 2007
    Location
    Plzeň, Czech Republic
    Posts
    10

    Solution

    Ok, I think the answer is:

    Code:
    with A as (
        select
            row_number() 
                over (partition by lastname order by firstname desc) as 'row'
            firstname
            lastname
        from
            users
    )
    select
        firstname
        lastname
    from
        A
    where
        row = 1
    Last edited by jsiii; 03-09-07 at 08:44.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Now I am not sure that returns what you want. Does is return the same as this?
    Code:
    SELECT lastname
      , MAX(firstname) AS firstname
    FROM dbo.A
    GROUP BY lastname
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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