Search for a higher date by a specific ID

Navigation
0

I have a problem with a SQL where I need to search for the largest date of a specified ID , where it has several records of this ID :

Data:

  

ID - date - name

     

2 - 10/10/2004 - cesar
5 - 10/10/2008 - joana
4 - 10/10/2002   - maria
1 - 04/01/2017 - paulo
1 - 12/12/2017 - paulo 4 - 10/10/2000 - maria 5 - 10/10/2017 - joana

In this example I would like to find the ID = 1 that corresponds to the name paulo and I need the highest date in specific, then I follow my SQL where I look for the largest date, but the name comes from another one and not from ID . 

SELECT MAX(data) as data, nome FROM teste WHERE id = '1'
  

Results ... 1 - 12/12/2017 - cesar      

Expected ... 1 - 12/12/2017 - paulo

That is, I realized that the first name of the Bank returns to me, and it is not what I need.

    
asked by anonymous 01.02.2018 / 13:02

3 answers

0

After much research I found the solution to my problem and I will review the solution !!

  

SELECT * FROM test WHERE data = (SELECT max (data) FROM test WHERE   id = 1)

Thank you all for your cooperation.

    
07.02.2018 / 13:56
1

You can solve everything with just one query doing: 

SELECT data, nome FROM teste WHERE id = '1' order by data desc limit 1;
    
01.02.2018 / 18:26
0

You can solve this by first doing the query by sorting by date after another query by limiting to 1. 

CREATE TABLE 'teste' (
  'id' int(11) NOT NULL,
  'data' datetime DEFAULT NULL,
  'nome' varchar(25) DEFAULT NULL
) 

INSERT INTO testeVALUES('2','2004-10-10','cesar'),('5','2008-10-10','joana'),
('4','2002-10-10','maria'),('1','2017-01-04','paulo'),('1','2017-12-12','paulo'),
('4','2000-10-10','maria'),('5','2017-10-10','joana');


SELECT
    aux.*
FROM(
SELECT 
    data as 'data', 
    nome 
FROM teste WHERE id = '1'
ORDER BY 'data' DESC
) AS aux
LIMIT 1
;

    
01.02.2018 / 13:09
Get TextBox value inside Repeater with JavaScript event Onchange Input number accept point in firefox