sql - SELECT TOP 1 MAX(DATETIME) -

sql - SELECT TOP 1 MAX(DATETIME) -

suppose have table called '@devicesample' , table called '@devicesactivitydatasample'.

i want select top 1 'max(datetime)' '@devicesactivitydatasample' each device when had duplicated rows in 'devicesactivitydatasample' in result duplicated rows looking top one. , problem when want select 100000 rows take long long time result because of calculating max 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 select 2,'device 2' union 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 select 2, '2013-02-15 03:30:24.000','hhjj' union select 2, '2013-02-15 03:30:24.000','hhjj' union select 2, '2013-02-15 03:30:24.000','hhjj' union select 1, '2013-02-16 03:30:24.000','hhjj' union select 1, '2013-02-11 03:30:24.000','hhjj' union select 2, '2013-02-10 03:30:24.000','hhjj' union select 3, '2013-02-07 04:25:24.000','hhjj' union select 3, '2013-02-12 23:02:02.000','hhjj' select * @devicesample inner bring together @devicesactivitydatasample on adeviceid = bdeviceid devicedatetime in ( select max(devicedatetime) maxdate @devicesactivitydatasample grouping bdeviceid ) go

and how can fast fast fast result 1000000 rows or more rows

this greatest n per grouping query. changing definition of 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 together useful index.

method 1 select dads.* @devicesample ds cross apply (select top 1 * @devicesactivitydatasample dads dads.bdeviceid = ds.adeviceid order dads.devicedatetime desc) dads; method 2 with t (select *, row_number() on (partition bdeviceid order devicedatetime desc) rn @devicesactivitydatasample) select * t rn = 1

you might consider using #temp table @aaron suggests perhaps next 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 need rowid @ all?

sql sql-server select