November 16, 2009 at 8:49 am
Hello folks,
I'd be grateful if people could cast their eye over my Select statement.
Is this the right way (or most efficient) to create a selection based on two columns, the first Where clause is a known reference, the second is an unknown maximum value?
IF OBJECT_ID('TempDB..#activity','U') IS NOT NULL
DROP TABLE #activity
--===== Create test table
CREATE TABLE #activity(
Ref [nchar](10) NULL,
Activity [nvarchar](max) NULL,
activitytype [smallint] NULL
)
--===== Insert the test data into the test table
INSERT INTO #activity
(Ref, Activity, activitytype)
SELECT '1 ','a','1' UNION ALL
SELECT '1 ','b','1' UNION ALL
SELECT '1 ','c','2' UNION ALL
SELECT '1 ','d','2' UNION ALL
SELECT '1 ','e','1' UNION ALL
SELECT '1 ','f','2' UNION ALL
SELECT '2 ','a','1' UNION ALL
SELECT '2 ','b','1' UNION ALL
SELECT '2 ','c','1' UNION ALL
SELECT '3 ','a','2'
--=====
--===== Select statement, where Ref =1 and the maximum of activitytype (an unknown but the max. is always required)
--=====
SELECT Ref
,Activity
,activitytype
FROM #activity
WHERE Ref = '1'
AND activitytype =
(SELECT max(activitytype)
FROM #activity
WHERE Ref = '1')
--=====
--===== clean up
--=====
DROP TABLE #activity
many thanks,
Dom Horton
November 16, 2009 at 9:36 am
There's at least two ways to achieve it. One is the code you posted, that can be changed to match the outer query and the subquery.
The other way that comes to my mind is using TOP 1:
-- MAX
SELECT Ref
,Activity
,activitytype
FROM #activity as ACT
WHERE Ref = '1'
AND activitytype = (
SELECT max(activitytype)
FROM #activity
WHERE Ref = ACT.Ref
)
-- TOP 1
SELECT Ref
,Activity
,activitytype
FROM #activity as ACT
WHERE Ref = '1'
AND activitytype = (
SELECT TOP 1 activitytype
FROM #activity
WHERE Ref = ACT.Ref
ORDER BY activitytype DESC
)
-- Gianluca Sartori
November 16, 2009 at 9:36 am
Edited: strange double post.
Anyway you have to test against your DB to decide which one is faster. It depends on your indexes and volumes, there's no single good answer. Check the execution plan and IO stats.
-- Gianluca Sartori
November 18, 2009 at 4:52 am
Hi Gianluca,
many thanks for your reply,
I wasn't sure that a nested/sub query was the way to go....thanks for confirming that method.
regards,
dom
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply