CODEWITHSUNDEEP
sql-server
Ephron Clarke
Ephron Clarke

Reputation: 77

I need assistance with a SQL sub-query

This query:

SELECT j.job_name, c.command, c.job_ver, j.job_ver
FROM AEDB.dbo.ujo_command_job as c
INNER JOIN AEDB.dbo.ujo_job as j 
ON c.joid = j.joid
WHERE command LIKE '%$$XI1134%'

returns the following sample rows:

XI113TEST4-J    'echo $$XI1134*-NXT-BUS-6D-MMdDDdYYYY'      6   1
XI113TEST4-J    'echo $$XI1134*-NXT-BUS-6D-MMdDDdYYYY'      6   2
XI113TEST4-J    'echo $$XI1134*-NXT-BUS-6D-MMdDDdYYYY'      6   3
XI113TEST4-J    'echo $$XI1134*-NXT-BUS-6D-MMdDDdYYYY'      6   4
XI113TEST4-J    'echo $$XI1134*-NXT-BUS-6D-MMdDDdYYYY'      6   5
XI113TEST4-J    'echo $$XI1134*-NXT-BUS-6D-MMdDDdYYYY'      6   6
XI113TEST4-J    'echo $$XI1134*-NXT-BUS-6D-MMdDDdYYYY'      6   7
XI113TEST4-J-1  'echo $$XI1134*-NXT-BUS-6D-YYJJJ'           2   1
XI113TEST4-J-1  'echo $$XI1134*-NXT-BUS-6D-YYJJJ'           2   2
XI113TEST4-J-1  'echo $$XI1134*-NXT-BUS-6D-YYJJJ'           2   3
XI113TEST4-J-2  'echo $$XI1134*-NXT-BUS-7D-TO-MMdDDdYYYY'   2   1
XI113TEST4-J-2  'echo $$XI1134*-NXT-BUS-7D-TO-MMdDDdYYYY'   2   2
XI113TEST4-J-2  'echo $$XI1134*-NXT-BUS-7D-TO-MMdDDdYYYY'   2   3
XI113TEST4-J-3  'echo $$XI1134*-NXT-BUS-7D-TO-YYJJJ'        3   1
XI113TEST4-J-3  'echo $$XI1134*-NXT-BUS-7D-TO-YYJJJ'        3   2
XI113TEST4-J-3  'echo $$XI1134*-NXT-BUS-7D-TO-YYJJJ'        3   3

How best can I modify the query so that the maximum value of j.job_ver (col 4) for any given jobname has to match c.job_ver (col 3). Only the last row in the above sample should result.

The database keeps job versions, and I don't want info from older versions returning.

Upvotes: 1

Views: 133

Answers (3)

Gen Wan
Gen Wan

Reputation: 2009

If I understand correctly, you only need 'group by' with max():

SELECT j.job_name, c.command, c.job_ver, max(j.job_ver)
FROM AEDB.dbo.ujo_command_job as c
INNER JOIN AEDB.dbo.ujo_job as j 
ON c.joid = j.joid
WHERE command LIKE '%$$XI1134%'
group by j.job_name, c.command, c.job_ver

If this does not work for you, could you explain 'Only the last row in the above sample should result'?

Only the last row for each category will return?

Another question is: what does 'col4 has to match col3' mean?

col4 = col3 or col4 >= col3

Update: Add one subquery to the above query:

SELECT a.job_name, a.command, a.job_ver, a.current_ver
from (
SELECT j.job_name, c.command, c.job_ver, max(j.job_ver) as current_ver
FROM AEDB.dbo.ujo_command_job as c
INNER JOIN AEDB.dbo.ujo_job as j 
ON c.joid = j.joid
WHERE command LIKE '%$$XI1134%'
group by j.job_name, c.command, c.job_ver )a
where a.job_ver = a.current_ver

Upvotes: 1

Shawn
Shawn

Reputation: 4786

There are a couple of ways you can do this. Since you only want records with the max ujo_job.job_ver, then you can either calculate this record in a CTE or do it in a sub-query. Depending on your data, one may be more performant than the other. You'll have to test.

See my Fiddle for the setup I used ( db<>fiddle here). If your data looks completely different, let me know and I'll take another stab at it.

CTE VERSION:

; WITH maxJobs AS (
    SELECT j.joid, j.job_name, max(j.job_ver) as job_ver
    FROM ujo_job j
    GROUP BY j.joid, j.job_name
)
SELECT j2.job_name, c.command, c.job_ver, j2.job_ver
FROM ujo_command_job c
INNER JOIN maxJobs j2 ON c.joid = j2.joid
    AND c.job_ver = j2.job_ver
WHERE c.command LIKE '%$$XI1134%'

job_name       | command                            | job_ver | job_ver
:------------- | :--------------------------------- | ------: | ------:
XI113TEST4-J-3 | echo $$XI1134*-NXT-BUS-7D-TO-YYJJJ |       3 |       3

SUB-QUERY VERSION:

SELECT s1.job_name, c.command, c.job_ver, s1.job_ver
FROM ujo_command_job c
INNER JOIN ( 
    SELECT j.joid, j.job_name, max(j.job_ver) as job_ver
    FROM ujo_job j
    GROUP BY j.joid, j.job_name
) s1 ON c.joid = s1.joid
    AND c.job_ver = s1.job_ver
WHERE c.command LIKE '%$$XI1134%'

job_name       | command                            | job_ver | job_ver
:------------- | :--------------------------------- | ------: | ------:
XI113TEST4-J-3 | echo $$XI1134*-NXT-BUS-7D-TO-YYJJJ |       3 |       3

I also changed your WHERE clause to do the LIKE after the first bit, rather than a wildcard at the start. If all of your commands start with "echo...", then do this to make it process a good bit faster. If they can start with something else, leave the wildcard % in the query.

EDIT: Due to the new OP comments, they may have any text before the search string, so I've switched back to the wildcard search on both ends of the term. This will make for more processing, so if the leading text is constant, leave it without the leading wildcard.

Upvotes: 1

iSR5
iSR5

Reputation: 3498

you only need to put that condition within your JOIN c.job_ver = j.job_ver

So, your query would like this : 

SELECT 
    j.job_name
,   c.command
,   c.job_ver
,   j.job_ver
FROM 
    AEDB.dbo.ujo_command_job as c
INNER JOIN AEDB.dbo.ujo_job as j 
ON c.joid = j.joid AND c.job_ver = j.job_ver
WHERE 
    command LIKE '%$$XI1134%'

Upvotes: 0

Related Questions