select max funtion question - SQL Server Forums

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

Author

Topic

phaze
Starting Member

42 Posts

Posted - 2010-04-22 : 18:02:16

I am writing a query to give me back distince accounts with the latest call date and was wondering if this looks correct

select distinct AccountNumber, CallDate, dDate, DialingStatus
from dbo.OutboundIVR
where CallDate in
(select max(CallDate)
from dbo.OutboundIVR
group by AccountNumber)

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-22 : 18:42:49

That's not going to give you what you want, but this might:

SELECT AccountNumber, CallDate, dDate, DialingStatus
FROM (		
		select AccountNumber, CallDate, dDate, DialingStatus,
			ROW_NUMBER() OVER (PARTITION BY AccountNumber ORDER BY CallDate DESC) AS Row
		from dbo.OutboundIVR ) z
WHERE Row = 1

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-23 : 02:33:04

[code]select t.AccountNumber, t.CallDate, t.dDate, t.DialingStatus
from dbo.OutboundIVR t
where t.CallDate in
(select max(CallDate)
from dbo.OutboundIVR
where AccountNumber= t.AccountNumber
)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

deepakugale
Starting Member

33 Posts

Posted - 2010-04-23 : 03:29:25

SELECT TEMP.AccountNumber,TEMP.CallDate,dDate, DialingStatus
from dbo.OutboundIVR AS OUT
INNER JOIN
(select AccountNumber, max(CallDate) AS CallDate
from dbo.OutboundIVR
group by AccountNumber) AS TEMP
ON TEMP.AccountNumber =OUT.AccountNumber AND TEMP.CallDate =OUT.CallDate

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-04-23 : 08:54:19

Use other solutions. It's just for fun!

SELECT *
  FROM dbo.OutboundIVR t1
 WHERE NOT EXISTS 
       (SELECT *
          FROM dbo.OutboundIVR t2
         WHERE AccountNumber= t1.AccountNumber
           AND t2.CallDate > t1.CallDate)

phaze
Starting Member

42 Posts

Posted - 2010-04-23 : 11:56:14

my final query was pretty close to visakh16's

thanks for the input fella's

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-04-23 : 12:08:05

quote:
Originally posted by phaze

my final query was pretty close to visakh16's

thanks for the input fella's

Because Visakh just changed your query and he did not write a new approach.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-23 : 12:45:59

quote:
Originally posted by ms65g

quote:
Originally posted by phaze

my final query was pretty close to visakh16's

thanks for the input fella's

Because Visakh just changed your query and he did not write a new approach.

thats the most easiest thing i thought. Atleast that will help him in understanding how that approach has to be

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-24 : 16:47:29

I did some testing on a table with 1,000,000 rows, created with this script:

WHILE @Count < 1000000
BEGIN
	SET @AccountNumber = RAND() * 1000
	SET @CallDate = DATEADD(s, rand() * 1000000, '20100101')

	IF NOT EXISTS (SELECT 1 FROM OutboundIVR
					WHERE @AccountNumber = AccountNumber AND @CallDate = CallDate )
	BEGIN
		INSERT INTO OutboundIVR
		SELECT @AccountNumber, @CallDate,
			DATEADD(d, rand() * 1000, '20100101'), RAND() * 10
		SET @Count = @Count + 1 
	END
END

I tested the methods posted by ms69g, deepakugale, visakh16, and myself.

Unindexed, here are the results.

DBA in the Making Method Start

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

(1000 row(s) affected)
Table 'OutboundIVR'. Scan count 1, logical reads 5730, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 1829 ms, elapsed time = 3034 ms.
visakh16 Method Start

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

(1000 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'OutboundIVR'. Scan count 2, logical reads 11460, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 922 ms, elapsed time = 1060 ms.
deepakugale Method Start

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

(1000 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'OutboundIVR'. Scan count 2, logical reads 11460, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 953 ms, elapsed time = 1009 ms.
ms65g Method Start

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

(1000 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'OutboundIVR'. Scan count 2, logical reads 11460, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 11313 ms, elapsed time = 12784 ms.

Using a clustered index:

DBA in the Making Method Start

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

(1000 row(s) affected)
Table 'OutboundIVR'. Scan count 1, logical reads 4097, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 422 ms, elapsed time = 532 ms.
visakh16 Method Start

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

(1000 row(s) affected)
Table 'OutboundIVR'. Scan count 1001, logical reads 7320, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 391 ms, elapsed time = 603 ms.
deepakugale Method Start

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

(1000 row(s) affected)
Table 'OutboundIVR'. Scan count 1001, logical reads 7320, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 390 ms, elapsed time = 458 ms.
ms65g Method Start

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

(1000 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'OutboundIVR'. Scan count 2, logical reads 8194, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 2938 ms, elapsed time = 4168 ms.

I also tried non clustered covering indexes, and unique clustered/non clustered indexes, but the results didn't vary that much.

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources