Query - help optimizing max(field)

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

SQL Server Development (2000)

Query - help optimizing max(field)

Author

Topic

purell
Starting Member

17 Posts

Posted - 2006-12-08 : 00:02:19

What is a better way to do this?

select name, number
from A1
where ACCTNUM=80989
and NB='1'
and TF='O'
and NC=(
select max(NC)
FROM
A1
where ACCTNUM=80989
and NB='1' )

Kristen
Test

22859 Posts

Posted - 2006-12-08 : 01:10:02

Looks OK to me. I would do a JOIN but that's probably personal preference.

Check the query plan and see if one is better than the other.

And index on ACCTNUM + NB would help.


select name, number
from   A1
       JOIN
    (
        select [MaxNC] = max(NC) 
        FROM   A1
        where      ACCTNUM=80989
               and NB='1'
    ) AS X
        ON X.MaxNC = A1.NC
where     ACCTNUM=80989
      and NB='1'
      and TF='O'

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-08 : 02:46:33

Similar topic http://sqlteam.com/forums/topic.asp?TOPIC_ID=76049

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-08 : 08:24:02

To get any AcctNum

SELECT		t.Name,
		t.Number
FROM		A1 t
INNER JOIN	(
			SELECT		AcctNum,
					MAX(NC) mnc
			FROM		A1
			WHERE		NB = '1'
			GROUP BY	AcctNum
		) d ON d.AcctNum = t.AcctNum AND d.mnc = t.NC
WHERE		t.NB = '1'
		and t.TF = 'O'
		and t.ACCTNUM = 80989 -- remove this line for all accts

Peter Larsson
Helsingborg, Sweden

Kristen
Test

22859 Posts

Posted - 2006-12-08 : 08:36:17

I reckon the inner nested query is too large, and needs to be constrained according to what the outer query is - personally!

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources