Inner Join display last status

Question

I need to get data from 3 tables through a filter in 2 of them using the Date and Status criteria, and the Date is done in one table and the Status in another.

It turns out that when more than one status with the same name it lists all, it would like to get the last status.

query used:

SELECT
numero.id,
numero.numero,
status.status,
status.data
FROM numero
INNER JOIN dados ON numero.id = dados.id_numero
INNER JOIN status ON numero.numero = status.numero AND status.status = 'Confirmado'
WHERE dados.data BETWEEN '2015-04-10' AND '2015-04-10 23:59:59.997' ORDER BY dados.data

Demonstrativo Online: SQL Fiddle

Answer 1

I understand that when you say, "last status" you intend to return only the one with the latest date. If so you can just do:

SELECT   numero.id,
         numero.numero,
         status.status,
         MAX(status.data) DATA
FROM numero
INNER JOIN dados 
   ON numero.id = dados.id_numero
INNER JOIN status 
   ON numero.numero = status.numero 
  AND status.status = 'Confirmado'
WHERE dados.data BETWEEN '2015-04-10' AND '2015-04-10 23:59:59.997' 
GROUP BY numero.id, numero.numero, status.status 
ORDER BY dados.data

Answer 2

Just make a GROUP BY numero.id

SELECT
  numero.id,
  numero.numero,
  status.status,
  status.data
FROM numero
INNER JOIN dados ON numero.id = dados.id_numero
INNER JOIN status ON numero.numero = status.numero AND status.status = 'Confirmado'
WHERE dados.data BETWEEN '2015-04-10' AND '2015-04-10 23:59:59.997'
GROUP BY numero.id
ORDER BY dados.data