Post reply

Selecting only the max row

  • July 5, 2006 at 12:54 pm

    #113525

    Lets say I have 2 seconds.

    ABC     T     5

    ABC     D     9

    How can I say 'return the max row'  (The column with 5 or 9).  Do I have to reference the table to itself to find the max number first then use a join to the orginal table on the max number??

    Basically I have a big view, but I only want to return the Max row. 

  • July 5, 2006 at 1:14 pm

    #647602

    I would probably use a query similar to this:

    SELECT *

         FROM [Table]

         WHERE [Column] = ( SELECT MAX([Column]) FROM [Table] )

  • July 5, 2006 at 3:39 pm

    #647630

    --If you only want to return one row you can use:

    select

    top 1 *

    from

    tab

    order

    by col desc

    --this is especially useful if you want to eliminate ties,
    --since you can add more columns to the ORDER BY clause.
    --
    --If you need to return more than one row
    --(i.e. you have a GROUP BY clause)
    --and you need to eliminate ties,
    --then things get a bit more complicated....

    Tim Wilkinson

    "If it doesn't work in practice, you're using the wrong theory"
    - Immanuel Kant

  • July 6, 2006 at 7:36 am

    #647749

    I'm returning more then one row, but that 'top 1' is a good idea to remember for other things.  Thanks guys.

  • July 6, 2006 at 4:25 pm

    #647859

    If you only want one or two values from the row, you can concatenate them before doing MAX, then extract them back out with SUBSTRING. This often requires converting some columns to CHAR(x).

    eg:

    SELECT SUBSTRING(MAX(CAST(column as char(10)) + CAST(othervalue as CHAR(8)), 11, 8) AS othervalue_for_max_of_column

    I used this technique to avoid creating temporary tables, etc, below.

    UPDATE [mytable]

    SET preferred = CASE WHEN p.rec_id IS NULL THEN 0 ELSE 1 END

    FROM [mytable] sf LEFT JOIN

     (SELECT CONVERT(INT, SUBSTRING(MAX(CONVERT(CHAR(14), upc_number)

     +CASE deleted when 0 then '1' else '0' END

     +CONVERT(CHAR(8),last_updated,112)

     +CONVERT(CHAR(10),rec_id)),24,10)) AS rec_id

     FROM [mytable]

     GROUP BY upc_number) AS p

     ON sf.rec_id = p.rec_id

    WHERE coalesce(preferred,-1) <> CASE WHEN p.rec_id IS NULL THEN 0 ELSE 1 END

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

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