Using MAX Function and return row other data from the Max Value

[jjtrap]

Hi everyone,
I'm new to this site and SQL, so I apologize if I repeat this question.

Within MS SQL 2000, I want to run a simple query to return the maxium value of the column col2.

I run:
SELECT Max(Table1.Col2) AS MaxOfCol2, Table1.Linker
FROM Table1
GROUP BY Table1.Linker;

And I get :
Max Linker
2 777

However what I REALLY want is to get:
Max Linker Col1
2 777 Second

I cannot just run:
SELECT Max(Table1.Col2) AS MaxOfCol2, Table1.Linker, Table1.Col1
FROM Table1
GROUP BY Table1.Linker;

One solution that I've come across is this:
SELECT Top 1 Col2, Linker, Col1
FROM Table1
Order By Col2 DESC;

This works; however, it is not practical because this is going to even a larger query I want to run.

Does anyone have any suggestions?

 

[gmmastros]

Try this...

[COLOR=blue]Select[/color] Table1.*
[COLOR=blue]From[/color]   Table1
       [COLOR=blue]Inner[/color] [COLOR=blue]Join[/color] (
         [COLOR=blue]SELECT[/color] [COLOR=#FF00FF]Max[/color](Table1.Col2) [COLOR=blue]AS[/color] MaxOfCol2, Table1.Linker
         [COLOR=blue]FROM[/color] Table1
         [COLOR=blue]GROUP[/color] [COLOR=blue]BY[/color] Table1.Linker
         ) [COLOR=blue]As[/color] A
         [COLOR=blue]On[/color] Table1.Linker = A.Linker
         And Table1.Col2 = A.MaxOfCol2

There is a problem with this query that should be noted. It may not affect you, but you should be aware of the potential problem.

Suppose you start with this data:

[tt][blue]
Linker Col1 Col2
----------- ---------- -----------
1 A 1
1 B 2
1 C 2
[/blue][/tt]

Notice that all 3 records have the same 'linker' value. Also notice that the max Col2 value is 2, but there are 2 rows that have that value. The query I show above will return the following 2 records as the result.

[tt][blue]
Linker Col1 Col2
----------- ---------- -----------
1 B 2
1 C 2
[/blue][/tt]

In the event of a 'tie' for col2, you could return the max of col1 or the min of col1, or even the average of col1 (assuming it's a number), like this...

[COLOR=blue]Select[/color] Table1.Linker,
       [COLOR=#FF00FF][!]Max[/!][/color](Table1.Col1) [COLOR=blue]As[/color] MaxOfCol1, 
       Table1.Col2
[COLOR=blue]From[/color]   @Table1 Table1
       [COLOR=blue]Inner[/color] [COLOR=blue]Join[/color] (
         [COLOR=blue]SELECT[/color] [COLOR=#FF00FF]Max[/color](Table1.Col2) [COLOR=blue]AS[/color] MaxOfCol2, Table1.Linker
         [COLOR=blue]FROM[/color] @Table1 Table1
         [COLOR=blue]GROUP[/color] [COLOR=blue]BY[/color] Table1.Linker
         ) [COLOR=blue]As[/color] A
         [COLOR=blue]On[/color] Table1.Linker = A.Linker
         And Table1.Col2 = A.MaxOfCol2
[COLOR=blue]Group[/color] [COLOR=blue]By[/color] Table1.Linker, Table1.Col2

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[jjtrap]

Thank you soo much! That second query helped out a lot!