CODEWITHSUNDEEP
sqlsql-serverselect
akaco
akaco

Reputation: 673

SELECT TOP 1 MAX(DATETIME)

Suppose I have a table called '@DeviceSample' and another table called '@DevicesActivityDataSample'.

I want To SELECT top 1 'MAX(DATETIME)' FROM '@DevicesActivityDataSample' for each Device BUT when I had Duplicated rows In 'DevicesActivityDataSample' In My result I get all Duplicated rows but I looking for top one. and another problem is when I want select from 100000 rows it take long long time to get my result because of calculating MAX this is my sample code 

DECLARE @DeviceSample TABLE
(
RowID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
ADeviceID INT ,
DeviceName VARCHAR(50) NOT NULL
) 
INSERT @DeviceSample
SELECT 1,'DEVICE 1' UNION ALL
SELECT 2,'DEVICE 2' UNION ALL
SELECT 3,'DEVICE 3'



DECLARE @DevicesActivityDataSample TABLE
(
RowID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
BDeviceID INT,
DeviceDateTime DATETIME,
CMD NVARCHAR(50)
) 

INSERT @DevicesActivityDataSample
SELECT 1, '2013-02-14 19:08:46.000','HHJJ' UNION ALL
SELECT 2, '2013-02-15 03:30:24.000','HHJJ' UNION ALL
SELECT 2, '2013-02-15 03:30:24.000','HHJJ' UNION ALL
SELECT 2, '2013-02-15 03:30:24.000','HHJJ' UNION ALL
SELECT 1, '2013-02-16 03:30:24.000','HHJJ' UNION ALL
SELECT 1, '2013-02-11 03:30:24.000','HHJJ' UNION ALL
SELECT 2, '2013-02-10 03:30:24.000','HHJJ' UNION ALL
SELECT 3, '2013-02-07 04:25:24.000','HHJJ' UNION ALL
SELECT 3, '2013-02-12 23:02:02.000','HHJJ' 



    SELECT  *
    FROM        @DeviceSample
    INNER JOIN  
    @DevicesActivityDataSample 
            ON  ADeviceID = BDeviceID
    WHERE       DeviceDateTime IN (
                    SELECT    MAX(DeviceDateTime) AS MaxDate
                    FROM      @DevicesActivityDataSample                                
                    GROUP BY  BDeviceID
                ) 

GO

and How can I get fast fast fast result for 1000000 rows or more rows

Upvotes: 0

Views: 11760

Answers (2)

Martin Smith
Martin Smith

Reputation: 453268

This is a greatest n per group query. Changing the definition of your table variable to

DECLARE @DevicesActivityDataSample TABLE (
  RowID          INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
  BDeviceID      INT,
  DeviceDateTime DATETIME,
  CMD            NVARCHAR(50),
  UNIQUE (BDeviceID, DeviceDateTime DESC, RowID, CMD) )

to add a useful index.

Method 1

SELECT DADS.*
FROM   @DeviceSample DS
       CROSS APPLY (SELECT TOP 1 *
                    FROM   @DevicesActivityDataSample DADS
                    WHERE  DADS.BDeviceID = DS.ADeviceID
                    ORDER  BY DADS.DeviceDateTime DESC) DADS;

Method 2

WITH T
     AS (SELECT *,
                ROW_NUMBER() OVER (PARTITION BY BDeviceID 
                                       ORDER BY DeviceDateTime DESC) AS RN
         FROM   @DevicesActivityDataSample)
SELECT *
FROM   T
WHERE  RN = 1

Plans

You might also consider using a #temp table as @Aaron suggests perhaps with the following definition.

CREATE TABLE #DevicesActivityDataSample (
  RowID          INT IDENTITY(1, 1) PRIMARY KEY NONCLUSTERED,
  BDeviceID      INT,
  DeviceDateTime DATETIME,
  CMD            NVARCHAR(50)) 

CREATE CLUSTERED INDEX IX ON #DevicesActivityDataSample(BDeviceID, DeviceDateTime DESC)

Do you actually need RowID at all?

Upvotes: 3

anon
anon

Reputation: 

SELECT DeviceID, MAX(DeviceDateTime)
FROM @DeviceSample
GROUP BY DeviceID;

If this is slow then consider using a #temp table instead of a @table variable (see the major differences here), and add an index like:

CREATE CLUSTERED INDEX LatestDeviceTime
  ON #DeviceSample (DeviceID, DeviceDateTime DESC);

Or not using a table variable or temp table in the first place, and adding a useful index to the base table where the data comes from.

Upvotes: 5

Related Questions