Select with joins return last result per date

Asked

Viewed 255 times

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 term. I tried to find a solution in many ways, but I failed in all of them. Usei group by, select MAX(vigencia) etc.

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

mysql

3 answers

3


The fact of being mysql makes it a little easier.

I returned the max(vigencia) and I grouped by Driver.id. My sql was 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 http://sqlfiddle.com/#! 2/c6832/1

  • Great! It worked perfectly. I tried everything but that. Thanks!

  • Edgar, like I said before, it worked. But it turns out that in some cases it’s not working, bro. What gets dirty? I create a query in Sqlfiddle for you to see or put the information here, in the above question?

  • @xmdenis make a fiddle and feed it with information to simulate the error.

1

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

  • 1

    The idea is good. As Edgar’s solution worked, then I test this too, for knowledge. But, thanks.

0

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

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

  • I accessed Sqlfiddle, and compared the result. Looks like it didn’t work. But, thanks.

  • @xmdenis what is wrong?

  • 1

    @xmdenis seems to me that your example of how this should be wrong, it shows the date of the drives table and not the duration of the Documents table that would be this: 543 | 2014-05-05 | 2014-05-15

  • you are right. I even made the correction in the example. But, I tried to apply your solution on my basis, but the effective date always comes to the oldest. Weird. I couldn’t see the reason. I had even tried this before. Anyway, thanks.

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.