Listar os dois últimos registros de cada id

[wdsf 0]

Boa Tarde!

 

Estou tendo problemas em listar os dois últimos registros de cada idmicro

idhist  idmicro idoutros  sala    unidade               Dtmov
100     1102        0         8      coa       2009-10-23 10:40:00.000
101     1102        0         1      coa       2009-10-28 10:40:00.000
102     1102        0         2       dib       2008-10-24 10:40:00.000
103     1201        0         6      diraf     2008-10-23 10:40:00.000
104     1201        0         7      diraf     2009-10-21 10:40:00.000
105     1201        0         4     dimel     2008-10-22 10:40:00.000

Ficaria assim:

Ex:

Resultado

idhist  idmicro idoutros  sala    unidade               Dtmov
101     1102        0         1      coa       2009-10-28 10:40:00.000
102     1102        0         2       dib       2008-10-24 10:40:00.000
103     1201        0         6      diraf     2008-10-23 10:40:00.000
104     1201        0         7      diraf     2009-10-21 10:40:00.000

Estou começando a me aprofundar no SQL e estou tendo problemas pra achar essa solução

 

 

Obrigado.

[advaldomesquita 93]

O resultado final não seria este:

idhist idmicro idoutros sala unidade Dtmov

101 1102 0 1 coa 2009-10-28 10:40:00.000

102 1102 0 2 dib 2008-10-24 10:40:00.000

104 1201 0 7 diraf 2009-10-21 10:40:00.000

105 1201 0 4 dimel 2008-10-22 10:40:00.000

Alterei o idhist do 1201

Correto?

[wdsf 0]

Isso!

 

Desculpa acabei errando!

[advaldomesquita 93]

Não testei performance, mas tente isso:

 

 

set nocount on 
 
declare @table table (idhist  int, idmicro int, idoutros  int, sala    int, unidade  varchar(100), Dtmov datetime)
 
insert @table select 100     ,1102        ,0         ,8      ,'coa       ','2009-10-23 10:40:00.000'
insert @table select 101     ,1102        ,0         ,1      ,'coa       ','2009-10-28 10:40:00.000'
insert @table select 102     ,1102        ,0         ,2      ,'dib       ','2008-10-24 10:40:00.000'
insert @table select 103     ,1201        ,0         ,6      ,'diraf     ','2008-10-23 10:40:00.000'
insert @table select 104     ,1201        ,0         ,7      ,'diraf     ','2009-10-21 10:40:00.000'
insert @table select 105     ,1201        ,0         ,4     ,'dimel     ','2008-10-22 10:40:00.000'
 
-- select * from @table
-- select count(*) as qtde,  idmicro from @table group by idmicro having COUNT(*) > 1
 
 
select * from @table a 
inner join (
select count(*) as qtde,  idmicro from @table group by idmicro having COUNT(*) > 1
) b
on a.idmicro = b.idmicro
inner join (select MAX(idhist) idh from @table group by idmicro) c
on a.idhist = c.idh
UNION ALL 
select * from @table a 
inner join (
select count(*) as qtde,  idmicro from @table group by idmicro having COUNT(*) > 1
) b
on a.idmicro = b.idmicro
inner join (select (MAX(idhist) - 1) idh from @table group by idmicro) d
on a.idhist = d.idh
 
ORDER BY 1
 

[wdsf 0]

Amigo. Funcionou! muito obrigado!