Select Max(FieldB) and Min(FieldC) Group by A

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Select Max(FieldB) and Min(FieldC) Group by A

Author

Topic

tm
Posting Yak Master

160 Posts

Posted - 2010-10-06 : 09:34:12

Hi,

I was wondering if there is a better way to write the query below using one statement.
As you can see below I am having to use SubQuery as I would like to group by A to get the Maximum of field B and then find the Mininum of C (Please see result at the bottom).

-- ============
declare @TableA table (a int, b datetime, c int)

insert into @TableA
select 1, '2010-09-01', 1
union all
select 1, '2010-09-02', 2
union all
select 1, '2010-09-02', 3
union all
select 2, '2010-09-01', 1
union all
select 2, '2010-09-02', 2

select TA.a, TA.b, min(TA.c) as c
from @TableA TA
inner join
(
select a, max(b) as bb
from @TableA
group by a
) TB
on TA.a = TB.a and TA.b = TB.bb
group by TA.a, TA.b

-- Result
a,b,c
1,2010-09-02 00:00:00.000,2
2,2010-09-02 00:00:00.000,2

Thanks in Advance,
TM

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2010-10-06 : 11:22:19

This is another solution. Which one is better? Depending on data and how big the table is. If columns a and b are already sorted and the table is large, your solution is better, else, this one is better, I think

.

;with temp as
(select a, max(b)as b from @TableA t2 group by a)
select a, b, (select min(c) from @TableA t1 where t1.a = t2.a and t1.b = t2.b) as c from temp t2

tm
Posting Yak Master

160 Posts

Posted - 2010-10-06 : 14:18:12

Thanks for your input namman.

Our table has millions of records and not sorted.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2010-10-06 : 15:02:47

I doubt this will be faster but it's worth a try. It seems to work with your sample data but not even sure if the logic will hold for your actual data.


select a, b, c
from   (
       select a, b, c, r = row_Number() over (partition by a order by b desc, c)
       from   @tablea
       ) d
where  r = 1

How long does your current code take?

Be One with the Optimizer
TG

tm
Posting Yak Master

160 Posts

Posted - 2010-10-07 : 11:51:23

I tried your query but with less records (a little over 610k). There is no difference in the time taken (11 seconds) to display results of under 600k records.

Thanks very much for the alternative query.

tm

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources