February 1, 2011 at 7:25 am
select A.Field1, A.ImportantDate, A.FirstTime
from T1 A
INNER JOIN (select Field1, max(FirstTime) AS FirstTime from T1
group by Field1) B
on A.Field1= B.Field1
and A.FirstTime = B.FirstTime
ORDER BY B.Field1, B.ImportantDate, B.FirstTime
Basically I'm trying to retrieve Field1 and ImportantDate on the basis of latest FirstTime per Field1.
February 1, 2011 at 8:54 am
sql_butterfly (2/1/2011)
select A.Field1, A.ImportantDate, A.FirstTimefrom T1 A
INNER JOIN (select Field1, max(FirstTime) AS FirstTime from T1
group by Field1) B
on A.Field1= B.Field1
and A.FirstTime = B.FirstTime
ORDER BY B.Field1, B.ImportantDate, B.FirstTime
Basically I'm trying to retrieve Field1 and ImportantDate on the basis of latest FirstTime per Field1.
No guarantees it'll be more efficient, but I frequently find that instead of a GROUP BY operation, to get the top value like that, I use a TOP with an ORDER BY. If it uses an index it makes for a very efficient operation.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
February 2, 2011 at 8:33 am
Thats true if you have only one Field1 value. However if you need resultset for lots of Field1 values, then TOP is not going to work.
February 3, 2011 at 5:39 am
I've always wanted to be able to say this:
It depends.
One alternative is to use ROW_NUMBER() to pick out the row with the max FirstTime - sample code below. Sometimes it was quicker for me, sometimes not - both queries produced different plans depending on the size of the table and the number of values for Field1. Unless your table has a clustered index on Field1, FirstTime DESC in which case ROW_NUMBER always wins, but I doubt you have that...
One possible issue with your code - do you want to produce exactly 1 row per Field1 value? For each Field1 are the values of FirstTime unique? If you have 2 rows with the same Field1 and same FirstTime, and it happens to be the max(FirstTime), your query will return both rows but the ROW_NUMBER() approach will return only 1.
IF OBJECT_ID('tempdb..#testData','U') IS NOT NULL DROP TABLE #testData
SELECT TOP (1000) -- use this to set how many records in the table
RAND(CHECKSUM(NEWID()))*7305 + CAST('2010' AS DATETIME) AS FirstTime,
RAND(CHECKSUM(NEWID()))*7305 + CAST('2010' AS DATETIME) AS ImportantDate,
CAST(RAND(CHECKSUM(NEWID()))*100 AS INT) AS Field1 -- use this to set (approximately) how many distinct values there are for Field1
INTO #testData
FROM master.sys.all_columns ac1
CROSS JOIN master.sys.all_columns ac2
CREATE CLUSTERED INDEX IX_testData_Field1
ON #testData (Field1)
select A.Field1, A.ImportantDate, A.FirstTime
from #testData A
INNER JOIN (select Field1, max(FirstTime) AS FirstTime from #testData
group by Field1) B
on A.Field1= B.Field1
and A.FirstTime = B.FirstTime
ORDER BY A.Field1, A.ImportantDate, A.FirstTime
; WITH cteOrdered AS (
SELECT Field1, ImportantDate, FirstTime, ROW_NUMBER() OVER (PARTITION BY Field1 ORDER BY FirstTime DESC) AS orderIdx
FROM #testData)
SELECT Field1, ImportantDate, FirstTime
FROM cteOrdered
WHERE orderIdx=1
February 7, 2011 at 7:49 am
Might not be quicker but it's a differnt way of writing it:
SELECT A.Field1,
A.ImportantDate,
A.FirstTime
FROM T1 A
WHERE EXISTS(SELECT 1
FROM T1 B
WHERE B.Field1 = B.Field1
GROUP BY B.Field1
HAVING MAX(B.FirstTime) = A.FirstTime)
February 7, 2011 at 1:44 pm
Are you looking to get a list of all the records or are you planning on adding a WHERE clause above and beyond your "show me the latest FirstTime" for each Field1?
I ask because you may gain from Jonathan's suggestion the most if you put your WHERE criteria within the EXISTS clause. Paul's suggestion is likely the best if you are showing all records.
Also, the number of fields in each table makes a difference and also the indexes available.
If their suggestions didn't make the query fast enough, please provide more info so we can assist.
--------------------------------------------------------------------------
When you realize you've dug yourself into a hole....Step 1...stop digging.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply