Select with joins return last result by date

Navigation
2

I have the tables drives, documents, and defenders:

Defenders 

id  | nome      | cpf
1   | Nome 1    | 000000
2   | Nome 2    | 000000
3   | Nome 3    |

Drives 

id   | document_id   | defender_id | data
1    | 123           | 1           | 2014-01-01
2    | 233           | 2           | 2013-05-02
3    | 453           | 1           | 2011-01-05
4    | 543           | 3           | 2014-05-20
5    | 454           | 1           | 2012-12-12
6    | 532           | 2           | 2011-11-05

Documents 

   id    | vigencia   | publicacao
   123   | 2014-01-05 | 2014-01-05 
   233   | 2013-05-02 | 2013-05-02 
   453   | 2011-01-05 | 2011-01-05 
   543   | 2014-05-05 | 2014-05-15 
   454   | 2012-12-13 | 2012-12-13 
   532   | 2011-11-05 | 2011-11-05

And my select: 

select d.nome, doc.vigencia from defenders d
    join drives dr on dr.defender_id = d.id
    join documents doc on doc.id = dr.document_id
group by nome
order by nome, doc.vigencia desc, doc.publicacao desc, dr.data_hora desc

I need to return something like: 

id   | nome      | vigencia
1    | Nome 1    | 2014-01-05
2    | Nome 2    | 2013-05-02
3    | Nome 3    | 2014-05-05

That is, only one record of each defenders ordered by force. I tried to find a solution in many ways, but I failed at all. I used group by, select MAX, etc.

Any way to do this? I could handle the result by PHP, but if you can bring it like this with the query, I prefer it.

    
asked by anonymous 26.08.2014 / 18:33

3 answers

3

Being mysql makes it a little easier.

I returned max and Grouped Driver.id . My sql looks like this: 

select 
  d.id,  
  d.nome, 
  max(doc.vigencia) as vigencia
from Defenders d 
inner join Drives di
  on d.id = di.defender_id
inner join Documents doc
  on di.document_id = doc.id
group by d.id

I tested the query on this link link

    
26.08.2014 / 19:32
1

Instead of a JOIN how about a subselect with LIMIT 1 and ORDER BY vigencia ?

    
26.08.2014 / 21:05
0

To get the result you just want to group by the ID of the defender, and sort by the name followed by the decreasing effective date: 

select d.nome, doc.vigencia from defenders d
    join drives dr on dr.defender_id = d.id
    join documents doc on doc.id = dr.document_id
group by d.id
order by d.nome, doc.vigencia desc;

SQLFiddle

    
26.08.2014 / 21:06
How to do a replace into from one server to another? Comparable and Arrays.Sort