Consult 3 tables and the result is by id the largest in 'Sql server'

Question

Consult 3 tables and the result is by id the largest in 'Sql server'

Navigation

#1 by (1 votes)
#2 by (1 votes)

1

I have 3 tables and I have the following query:

select S.ID, C.IDSENSOR, C.ID, CF.IDCANAL, MAX(CF.ENTRY_ID) as entry, cf.estado
  from Sensor as S
    join  Canal as C on S.Id = C.IdSensor
    join  CanalFeed as CF on C.Id = CF.IdCanal
where S.Id = 1 
GROUP BY S.ID,C.IDSENSOR,C.ID,CF.IDCANAL, cf.estado
order by CF.IDCANAL

I get the following result:

How would you change that query so that it brings the largest (ENTRY_ID) for each channel id? Marco in red as I just want to bring the query.

Edition

How would you bring me only those marked in red because it would be for each channel id bring me the largest one by ENTRY

sql sql-server

asked by Alcides Salazar 14.03.2018 в 02:03

source

2 answers

1

you must use MAX(CAMPO) for entry_id and agrupar for the other fields test as follows

select  S.ID, C.IDSENSOR, C.ID, CF.IDCANAL, MAX(CF.ENTRY_ID)
from Sensor as S
    join  Canal as C on S.Id = C.IdSensor
    join  CanalFeed as CF on C.Id = CF.IdCanal
where S.Id = 1 AND cf.estado = '@cheerlights green'
GROUP BY S.ID,C.IDSENSOR,C.ID,CF.IDCANAL
order by CF.IDCANAL

answered by 14.03.2018 в 03:19

How can I validate if a file exists or not? The 'X' method could not be found in the 'Z' activity

score 1 · Accepted Answer

What you can do is sort based on this Entry_Id and get your position according to the highest to the lowest. Then it would simply be enough to keep the numbers 1 (the largest)

select * from 
(
select S.ID, C.IDSENSOR, C.ID CanalId, CF.IDCANAL, MAX(CF.ENTRY_ID) as entry, cf.estado,
DENSE_RANK() over (partition by CF.IDCANAL order by MAX(CF.ENTRY_ID) desc) rnk
  from Sensor as S
    join  Canal as C on S.Id = C.IdSensor
    join  CanalFeed as CF on C.Id = CF.IdCanal
where S.Id = 1 
GROUP BY S.ID,C.IDSENSOR,C.ID,CF.IDCANAL, cf.estado
) CF
where rnk = 1
order by CF.IDCANAL

With this I should go out as you want