Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2008
    Posts
    4

    Unanswered: select max question

    Hi All,
    I have a table with 3 columns.

    ActNr, plnNr, duration

    i want to select ActNr, PlnNr with maximum duration.
    i tryid this:

    SELECT MAX(duration), ActNr, PlnNr FROM table WHERE ActNr='1'
    GROUP BY ActNr, PlnNr

    Bud the query returns all rows with ActNr 1, and not the maximum of duration

    What is wrong here? can anyone help me?

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You want to find the PlnNr with the maximum duration? Could you supply some sample data and then tell us what you want the result to be - unfortunately, queries that don't work aren't very useful for us
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    SELECT *
    FROM   your_table As a
     INNER
      JOIN (
            SELECT Max(duration) As max_duration
            FROM   your_table
           ) As b
        ON a.duration = b.max_duration
    George
    Home | Blog

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    SELECT TOP 1 * FROM table WHERE ActNr='1'
    ORDER BY duration DESC

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Dec 2008
    Posts
    135
    try like this it will works from 2005 onwards
    select actnr, max(duration)over(partition by PlnNr ) , PlnNr from table
    where actnr='1'

  6. #6
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Code:
    SELECT *
    FROM   your_table As a
     INNER
      JOIN (
            SELECT Max(duration) As max_duration
            FROM   your_table
           ) As b
        ON a.ActNr = b.ActNr AND
        a.duration = b.max_duration
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  7. #7
    Join Date
    Nov 2002
    Posts
    272
    Quote Originally Posted by Wim
    Code:
    SELECT *
    FROM   your_table As a
     INNER
      JOIN (
            SELECT Max(duration) As max_duration
            FROM   your_table
           ) As b
        ON a.ActNr = b.ActNr AND
        a.duration = b.max_duration
    How nice of you to indicate in red where the query goes wrong

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    SELECT *
    FROM   your_table As a
     INNER
      JOIN (
            SELECT Max(duration) As max_duration
                 , ActNr
            FROM   your_table
            GROUP
                BY ActNr
           ) As b
        ON a.ActNr = b.ActNr AND
        a.duration = b.max_duration
    George
    Home | Blog

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Can I play?
    Code:
    SELECT *
    FROM   your_table AS me_other_data
    CROSS APPLY
        (
            SELECT    max_duration            = MAX(duration)
            FROM    your_table                AS me_data
            WHERE    me_data.ActNr            = me_other_data.ActNr
            HAVING    Max(me_data.duration)    = me_other_data.duration
        ) AS me_data
    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
  •