DISTINCT and MAX in SQL Query
-
Hello friends, I need a optimized query for below condition CaseName ScriptName ID Status Version --------------------------- TC1 TS1 1 PASS R1 TC1 TS1 2 FAIL R1 TC1 TS1 3 PASS R1 TC2 TS1 1 PASS R1 TC2 TS1 1 PASS R1 TC3 TS1 1 PASS R1 TC1 TS2 4 PASS R2 TC1 TS3 5 PASS R2 Want to get distinct casename, ScriptName,status,Max(ID),version Result I need is TC1 TS1 3 PASS R1 TC2 TS1 1 PASS R1 TC3 TS1 1 PASS R1 TC1 TS2 4 PASS R2 TC1 TS3 5 PASS R2 When i tried using the below queries, both query taking hell lot of time(seems it may stuck). Table contains atleast 7 lakhs(700 thousands) of records. SELECT DISTINCT CaseName,ScriptName,ID,Status,Version FROM Metrics AS t1 WHERE ID = (SELECT MAX(ID) FROM Metrics WHERE CaseName = t1.CaseName AND ScriptName = t1.ScriptName AND Version = t1.Version)" SELECT CaseName,ScriptName,ID,Status,Version FROM Metrics WHERE ID IN (SELECT MAX(ID) from Metrics GROUP BY CaseName,ScriptName,Version)" Thanks in Advance Jishith
-
Hello friends, I need a optimized query for below condition CaseName ScriptName ID Status Version --------------------------- TC1 TS1 1 PASS R1 TC1 TS1 2 FAIL R1 TC1 TS1 3 PASS R1 TC2 TS1 1 PASS R1 TC2 TS1 1 PASS R1 TC3 TS1 1 PASS R1 TC1 TS2 4 PASS R2 TC1 TS3 5 PASS R2 Want to get distinct casename, ScriptName,status,Max(ID),version Result I need is TC1 TS1 3 PASS R1 TC2 TS1 1 PASS R1 TC3 TS1 1 PASS R1 TC1 TS2 4 PASS R2 TC1 TS3 5 PASS R2 When i tried using the below queries, both query taking hell lot of time(seems it may stuck). Table contains atleast 7 lakhs(700 thousands) of records. SELECT DISTINCT CaseName,ScriptName,ID,Status,Version FROM Metrics AS t1 WHERE ID = (SELECT MAX(ID) FROM Metrics WHERE CaseName = t1.CaseName AND ScriptName = t1.ScriptName AND Version = t1.Version)" SELECT CaseName,ScriptName,ID,Status,Version FROM Metrics WHERE ID IN (SELECT MAX(ID) from Metrics GROUP BY CaseName,ScriptName,Version)" Thanks in Advance Jishith
SELECT CaseName,ScriptName,ID,Status,Version FROM Metrics a WHERE EXISTS(SELECT CaseName FROM Metrics WHERE a.CaseName=CaseName AND a.ScriptName=ScriptName AND a.Version = Version GROUP BY CaseName,ScriptName,Version HAVING MAX(ID)=a.ID) I hope it can reduce your time
-
Hello friends, I need a optimized query for below condition CaseName ScriptName ID Status Version --------------------------- TC1 TS1 1 PASS R1 TC1 TS1 2 FAIL R1 TC1 TS1 3 PASS R1 TC2 TS1 1 PASS R1 TC2 TS1 1 PASS R1 TC3 TS1 1 PASS R1 TC1 TS2 4 PASS R2 TC1 TS3 5 PASS R2 Want to get distinct casename, ScriptName,status,Max(ID),version Result I need is TC1 TS1 3 PASS R1 TC2 TS1 1 PASS R1 TC3 TS1 1 PASS R1 TC1 TS2 4 PASS R2 TC1 TS3 5 PASS R2 When i tried using the below queries, both query taking hell lot of time(seems it may stuck). Table contains atleast 7 lakhs(700 thousands) of records. SELECT DISTINCT CaseName,ScriptName,ID,Status,Version FROM Metrics AS t1 WHERE ID = (SELECT MAX(ID) FROM Metrics WHERE CaseName = t1.CaseName AND ScriptName = t1.ScriptName AND Version = t1.Version)" SELECT CaseName,ScriptName,ID,Status,Version FROM Metrics WHERE ID IN (SELECT MAX(ID) from Metrics GROUP BY CaseName,ScriptName,Version)" Thanks in Advance Jishith
-
Is it helpful?
Select distinct CaseName , ScriptName , max(ID),Status ,Version
from Metrics Where Status = 'PASS'
Group By CaseName , ScriptName ,Status ,VersionHi vica dianto, This Query also takng much time.. but much faster than what i posted. I want to insert these result in an access table also. Is the insertion creating the touble or the query itself? Hi abcurl, "Status = 'PASS'".. Status can be fail or pass, but if two similar Cases having different status I want the lastest status, ie Case having Laresgt ID. One more thing i want the result to be inserted in an access table also Thanks alot for the replies Jishith