LabadaEfel2 LabadaEfel2 - 1 year ago 76
MySQL Question

SQL If records are repetitive select the one with the max id

I have a query that returns older versions of some records (due to bad database design) and i try to get only the newest ones. So i try to get the ones with Max Id's but query is complicated and there are lots of records. So subselect queries like this wont work:

SELECT row
FROM table
WHERE id=(
SELECT max(id) FROM table
)


And here's my query. By the way my company is using Testlink, if you're familiar with it and you have a better solution for this, it would be appreciated.

SELECT req_doc_id, scope,nh.parent_id,nh.id
FROM (SELECT nh.id, req_doc_id,doc_id FROM nodes_hierarchy nh,
req_specs r1, requirements r2
WHERE nh.parent_id = r1.id AND nh.id = r2.id
AND (r1.doc_id LIKE '%BlaBla%' OR r1.doc_id LIKE '%Tralala%'))
AS t1, nodes_hierarchy nh, req_versions r1
WHERE t1.id = nh.parent_id AND nh.id = r1.id


And resultset is


NAME..................ID




BlaBla1..............163

BlaBla1..............190

BlaBla2..............173

Tralala..............15

Tralala2.............26

Tralala2.............19


What I want is:


NAME..................ID




BlaBla1..............190

BlaBla2..............173

Tralala..............15

Tralala2.............26

Answer Source

Here you go:

 SELECT Name,Notes
 FROM (select nh.id ,req_doc_id as Name, scope as Notes  from 
 (select nh.id,req_doc_id,doc_id  from nodes_hierarchy nh ,
 req_specs r1, requirements r2 
 where nh.parent_id = r1.id  and nh.id = r2.id 
 and ( r1.doc_id like '%Tralala%'
 or r1.doc_id like '%BlaBla' ) )    

 as t1,
 nodes_hierarchy nh, req_versions r1 
 where t1.id = nh.parent_id  and nh.id = r1.id ) AS TABLO
 INNER JOIN(
 SELECT MAX(nh.id) ID ,scope FROM  (select nh.id,req_doc_id,doc_id  from nodes_hierarchy nh ,
 req_specs r1, requirements r2 
 where nh.parent_id = r1.id  and nh.id = r2.id 
 and (r1.doc_id like '%Tralala%'
 or r1.doc_id like '%BlaBla' ) )  

 as t1, nodes_hierarchy nh, req_versions r1 where t1.id = nh.parent_id  and nh.id = r1.id GROUP BY req_doc_id)
 INNERTABLE on INNERTABLE.ID = TABLO.ID;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download