SQL If the records are duplicate, select the one with the maximum id
I have a query that returns older versions of some records (due to poor database design) and I am trying to get only the newest ones. So I am trying to get them with Max Id, but the query is complex and there are many records. So, subqueries like this won't work:
SELECT row
FROM table
WHERE id=(
SELECT max(id) FROM table
)
And here's my request. BTW my company is using Testlink, if you are familiar with it and have a better solution for this it would be helpful.
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 resultet
NAME..................ID
____
BlaBla1..............163
BlaBla1..............190
BlaBla2..............173
Tralala..............15
Tralala2.............26
Tralala2.............19
I want to:
NAME..................ID
____
BlaBla1..............190
BlaBla2..............173
Tralala..............15
Tralala2.............26
+3
user8368320
source
to share
2 answers
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;
-1
user8369262
source
to share