SQL Nested Select to query from a list

Question

I need list with Columns as defined below.

Agent Name	Job_Name	Client_id	Last Run Timestamp
Agent-1	Job 11	10	08.06.2022 00:30:32
Agent-1	Job 12	10	04.06.2022 00:31:02
Agent-1	Job 21	10	08.06.2022 00:30:32
Agent-1	Job 31	10	07.06.2022 00:31:02
Agent-1	Job 32	10	05.06.2022 00:31:02

Multiple Jobs will be running multiple times in a day or month in an agent.

I seek to get the list with only the latest (top 1 from timestamp desc) run with timestamp for all jobs on the agent.

However, I'm getting millions of rows with duplicate of jobs from same server.

select Agent_Name, Job_Name, Client_id, Last_Run_Timestamp
from table1
  join table2 on table.idnr = table1.idnr
where client_id = 10
  and exists (select job_name from listofjobs)
order by Agent_Name, timestamp desc

https://meta.stackoverflow.com/questions/388759/why-should-i-tag-my-rdbms — Reporter, Jun 08 '22 at 08:22
[how to ask on dba](https://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=&cad=rja&uact=8&ved=2ahUKEwiM4bTNvJ34AhXNyKQKHerdAB8QFnoECAgQAQ&url=https%3A%2F%2Fdba.stackexchange.com%2Fhelp%2Fhow-to-ask&usg=AOvVaw1QPqAMqrOWWzkoWbI3MhOR) — user14063792468, Jun 08 '22 at 08:50
The EXISTS subquery needs to be _correlated_, i.e. include a condition referencing table1 or table2. — jarlh, Jun 08 '22 at 10:10
|Table 1 - Jobname, Job_Idnr, Client_id| |Table 2 - Job_idnr, Agent_Name, Last_Run_Timestamp| — user1940594, Jun 09 '22 at 11:13

score 0 · Answer 1 · answered Jun 08 '22 at 09:23

0

Try using the WITH clause to first get the latest agent and then get all the results you want.

WITH with_latest_agent AS
  (SELECT top 1 agent_name
   FROM table1 TIMESTAMP DESC)
SELECT Job_Name,
       TIMESTAMP
FROM table1
WHERE agent_name in
    (SELECT agent_name
     FROM with_latest_agent)
ORDER BY name,
         TIMESTAMP DESC;

answered Jun 08 '22 at 09:23

Kadabra

1
1

I'm getting millions of records. Which is not as desired. Ok. Let's stick to one agent. In this agent we have 550 jobs running in different interval and times from last few years ( few runs every 1 hr and few runs daily once and few monthly once). Now I need to get 250 jobs with last timestamp it was started.. Can you please help. How can we loop these 550 jobs to get the last run. – user1940594 Jun 09 '22 at 10:07
Your question seems similar to this one : https://stackoverflow.com/questions/121387/fetch-the-row-which-has-the-max-value-for-a-column – Kadabra Jun 10 '22 at 11:12
No above solution did not work for me. Can we have some Stored Procedure? Something like this needed in oracle. https://www.c-sharpcorner.com/article/iterate-through-array-of-data-in-sql-query/ – user1940594 Jun 13 '22 at 12:57

SQL Nested Select to query from a list

1 Answers1