0

I have a query that returns a result fairly quickly:

SELECT [Date],[AccountCode],[ModelCode],[Generic],SUM([MTMusd]) AS 'MTMusd'
FROM [dbo].[MTM]
WHERE [AccountCode] = 'XXX'
AND [ModelCode] = '1'
GROUP BY [Date],[AccountCode],[ModelCode],[Generic]
ORDER BY [Date]


CREATE TABLE [dbo].[MTM](
    [AccountCode] [nvarchar](50) NULL,
    [ModelCode] [nvarchar](50) NULL,
    [Date] [date] NULL,
    [TIMESTAMP] [time](7) NOT NULL,
    [Generic] [nvarchar](50) NULL,
    [MTMUsd] [float] NOT NULL
) ON [PRIMARY]

However, I don't want SUM() I want LAST().

In the GROUP BY I have missed out [TIMESTAMP] which is the time of day. I want my query to return the record with the LAST TIMESTAMP for each GROUP { [Date],[AccountCode],[ModelCode],[Generic] }

How can I achieve this efficiently? I have approx 5 millions rows.

I tried ROW_NUMBER() OVER () based example I found, but processing took an order of minutes.

mendosi
  • 2,001
  • 1
  • 12
  • 18
ManInMoon
  • 6,795
  • 15
  • 70
  • 133
  • I don't want MAX(TIMESTAMP) I want MTMusd for the LAST(TIMESTAMP) – ManInMoon Nov 07 '16 at 10:05
  • `ROW_NUMBER` is one standard way to do it. Read http://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group and http://dba.stackexchange.com/questions/86415/retrieving-n-rows-per-group for comparison of different methods and ideas how to make the query fast (you'll need proper index). – Vladimir Baranov Nov 07 '16 at 10:13
  • This question looks slightly more complex than many, and the answer would probably include consideration of indexing. Might be better to post at dba.stackexchange.com? – mendosi Nov 07 '16 at 10:47
  • For starters, an index on `AccountCode, ModelCode` would help filtering out the rows before they're even grouped (assuming you need far less than all 5 million rows). – Jeroen Mostert Nov 07 '16 at 11:41
  • Looks like the table has no clustered index (?) Is it possible to create one? Even an non-clustered index on `AccountCode, ModelCode` might result in a substantial number of RID lookups and if you tried to make it a covering index for this query then you have almost duplicated the table. But if you could cluster the table by `AccountCode, ModelCode, Date, Timestamp` maybe the performance problem starts to go away without abandoning `Row_Number()` – mendosi Nov 07 '16 at 11:52
  • Odd that SUM is very fast even with no clever indexing/clustering. Pity TOP could not be used in someway. – ManInMoon Nov 07 '16 at 13:33

0 Answers0