Selecting only the max row

Question

Post reply

Selecting only the max row

Matt Boiteau

SSC-Addicted

Points: 461
More actions
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.

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

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

Julie Hargraves SSCarpal Tunnel Points: 4457 More actions · Answer 1

I would probably use a query similar to this:

SELECT *

FROM [Table]

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

stax68 SSChampion Points: 11711 More actions · Answer 2

--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

Matt Boiteau SSC-Addicted Points: 461 More actions · Answer 3

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

Aaron West SSC Eights! Points: 808 More actions · Answer 4

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