Max function - 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)

Max function

Author

Topic

davidhills
Starting Member

14 Posts

Posted - 2009-12-15 : 04:59:02

Good morning

Is there a more elegant solution to this query

SELECT rh.user_name, rh.report_name, rh.report_run_date
FROM report_history rh,
(SELECT max(report_run_date) as maxdate, report_name
FROM report_history
GROUP BY report_name) maxresults
WHERE rh.report_name = maxresults.report_name
AND rh.report_run_date= maxresults.maxdate;

it seems inefficient to have to use the outer query to get the
report_run_date because at some stage during the execution of the inner query you had the record in memory.

Can you use sql window functions somehow?

Thanks

David

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-12-15 : 05:38:32

I think ROW_NUMBER() would actually be slower for this. (GROUP BY with MAX is quicker I think).

You could try:


DECLARE @report_history TABLE (
	[user_name] VARCHAR(50)
	, [report_name] VARCHAR(50)
	, [report_run_date] DATETIME
	)

INSERT @report_history ([user_name], [report_name], [report_run_date])
      SELECT 'bRubble', 'Foot Breakage Report', '20090101'
UNION SELECT 'fFlintstone', 'Foot Breakage Report', '20090101'
UNION SELECT 'wFlintstone', 'Foot Breakage Report', '20081231'
UNION SELECT 'bamBAM', 'Club Breakage Report', '20090101'

SELECT
	[user_name]
	, [report_name]
	, [report_run_date]
FROM
	(
		SELECT
			[user_name]
			, [report_name]
			, [report_run_date]
			, ROW_NUMBER() OVER(PARTITION BY [report_name] ORDER BY [report_run_date] DESC, [user_name]) AS [POS]
		FROM
			@report_history
	)
	t
WHERE
	t.[pos] = 1

And see if that is slower or faster.

NB -- the sql I posted may not give you the same results as your current query which would actually have brought back everyone from my example table except for wFlintstone).

You are trying to pull back:

For each report, the last time it was ran and who ran it?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2009-12-15 : 06:31:24

You can use RANK() rather than ROW_NUMBER() if ties are needed. You'll need to delete user_name from the ORDER BY though.

Ryan Randall - Yak of all trades
Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources