Is there a more efficient way?

15 Jul 2005 1:15 AM

Michael C

I want to retreive the records for when a certain column has a maximum value
in a group. Using MAX i get the maximum value but what I really want is the
row, not the value. I can do it with a subquery but it doesn't seem that
efficient. My actual situation is much more complex involving joins from 5
tables and the way I'm doing it I'll need to include each of those 5 tables
in the query twice. One of the tables has a large amount of data so the
query could get quite slow.

Many thanks,
Michael

The rows with the -- are the ones I want where C is a maximum for the group
of A and B.

CREATE TABLE ABC(ID INT, A INT, B INT, C INT)
INSERT INTO ABC(ID,A,B,C) VALUES (1,1,1,1)
INSERT INTO ABC(ID,A,B,C) VALUES (2,1,1,2)
INSERT INTO ABC(ID,A,B,C) VALUES (3,1,1,3)--
INSERT INTO ABC(ID,A,B,C) VALUES (4,5,11,1)
INSERT INTO ABC(ID,A,B,C) VALUES (5,5,11,2)--
INSERT INTO ABC(ID,A,B,C) VALUES (6,5,12,1)--

SELECT ID FROM
(
SELECT A, B, MAX(C) AS C FROM ABC GROUP BY A,B
) AS tbl
JOIN ABC ON tbl.A = ABC.A AND tbl.B = ABC.B AND tbl.C = ABC.C

DROP TABLE ABC

15 Jul 2005 1:25 AM

Robbe Morris [C# MVP]

you mean like this?

CREATE PROCEDURE dbo.GetMyResultSet as

declare @MyMaxCID int
declare @MyMax int

select top 1 @MyMaxCID = tableC.PrimaryKeyID,
                   @MyMax = SomeColumnValue
   from tableC
order by SomeColumnYouWereDoingAMaxOn desc

Now, run your other query with the join using your
newly found primary key from tableC.

select tableB.*,tableA.*,@MyMaxCID as MyID,@MyMax as MyMax
from TableB,tableA
where blah,blah, blah
    and some join tableB.SomeKey = @MyMaxCID

--
2004 and 2005 Microsoft MVP C#
Robbe Morris
http://www.masterado.net

Earn $$$ money answering .NET Framework
messageboard posts at EggHeadCafe.com.
http://www.eggheadcafe.com/forums/merit.asp

Show quote

"Michael C" <mculley@NOSPAMoptushome.com.au> wrote in message
news:uffGunNiFHA.3540@TK2MSFTNGP14.phx.gbl...
>I want to retreive the records for when a certain column has a maximum
>value in a group. Using MAX i get the maximum value but what I really want
>is the row, not the value. I can do it with a subquery but it doesn't seem
>that efficient. My actual situation is much more complex involving joins
>from 5 tables and the way I'm doing it I'll need to include each of those 5
>tables in the query twice. One of the tables has a large amount of data so
>the query could get quite slow.
>
> Many thanks,
> Michael
>
> The rows with the -- are the ones I want where C is a maximum for the
> group of A and B.
>
> CREATE TABLE ABC(ID INT, A INT, B INT, C INT)
> INSERT INTO ABC(ID,A,B,C) VALUES (1,1,1,1)
> INSERT INTO ABC(ID,A,B,C) VALUES (2,1,1,2)
> INSERT INTO ABC(ID,A,B,C) VALUES (3,1,1,3)--
> INSERT INTO ABC(ID,A,B,C) VALUES (4,5,11,1)
> INSERT INTO ABC(ID,A,B,C) VALUES (5,5,11,2)--
> INSERT INTO ABC(ID,A,B,C) VALUES (6,5,12,1)--
>
> SELECT ID FROM
> (
> SELECT A, B, MAX(C) AS C FROM ABC GROUP BY A,B
> ) AS tbl
> JOIN ABC ON tbl.A = ABC.A AND tbl.B = ABC.B AND tbl.C = ABC.C
>
> DROP TABLE ABC
>

15 Jul 2005 3:48 AM

Uri Dimant

Mike
try
select [id] from abc where
c=(select max(c) from abc a where a.a=abc.a and a.b=abc.b)

Show quote

"Michael C" <mculley@NOSPAMoptushome.com.au> wrote in message
news:uffGunNiFHA.3540@TK2MSFTNGP14.phx.gbl...
> I want to retreive the records for when a certain column has a maximum
value
> in a group. Using MAX i get the maximum value but what I really want is
the
> row, not the value. I can do it with a subquery but it doesn't seem that
> efficient. My actual situation is much more complex involving joins from 5
> tables and the way I'm doing it I'll need to include each of those 5
tables
> in the query twice. One of the tables has a large amount of data so the
> query could get quite slow.
>
> Many thanks,
> Michael
>
> The rows with the -- are the ones I want where C is a maximum for the
group
> of A and B.
>
> CREATE TABLE ABC(ID INT, A INT, B INT, C INT)
> INSERT INTO ABC(ID,A,B,C) VALUES (1,1,1,1)
> INSERT INTO ABC(ID,A,B,C) VALUES (2,1,1,2)
> INSERT INTO ABC(ID,A,B,C) VALUES (3,1,1,3)--
> INSERT INTO ABC(ID,A,B,C) VALUES (4,5,11,1)
> INSERT INTO ABC(ID,A,B,C) VALUES (5,5,11,2)--
> INSERT INTO ABC(ID,A,B,C) VALUES (6,5,12,1)--
>
> SELECT ID FROM
> (
> SELECT A, B, MAX(C) AS C FROM ABC GROUP BY A,B
> ) AS tbl
> JOIN ABC ON tbl.A = ABC.A AND tbl.B = ABC.B AND tbl.C = ABC.C
>
> DROP TABLE ABC
>
>

20 Jul 2005 5:12 AM

Michael C

"Uri Dimant" <u***@iscar.co.il> wrote in message
news:uoVLdAPiFHA.2472@TK2MSFTNGP15.phx.gbl...
> Mike
> try
> select [id] from abc where
> c=(select max(c) from abc a where a.a=abc.a and a.b=abc.b)

Thanks uri, we used a variation of this and it worked quite well except I
did it the opposite way around, excluding records that are less than the
max. Most of the time there won't be more than one record per group anyway
so it is quicker to exclude those less than max.

Michael