Any ideas why this works:

SELECT NETINT.NETWORKID
FROM MSTRESRC.NETWORKINTERFACES AS NETINT
LEFT OUTER JOIN MSTRESRC.NETWORKS AS NETWORK ON NETWORK.NETWORKID=NETINT.NETWORKID
WHERE NETINT.NODEID=8713
ORDER BY NETWORK.NETMASK
FETCH FIRST ROW ONLY


but this does not:

SELECT HOSTNAME
FROM MSTRESRC.NETWORKINTERFACES
WHERE NODEID=8713
AND NETWORKID=(
SELECT MSTRESRC.NETWORKINTERFACES.NETWORKID
FROM MSTRESRC.NETWORKINTERFACES AS NETINT
LEFT OUTER JOIN MSTRESRC.NETWORKS AS NETWORK ON NETWORK.NETWORKID=NETINT.NETWORKID
WHERE NETINT.NODEID=8713
ORDER BY NETWORK.NETMASK
FETCH FIRST 1 ROWS ONLY)

I intend to use the second version to create a column in a bigger query
where each row of the bigger query would be for per NODE. The net
is I cannot get FETCH FIRST to work in a subquery...

Thanks in advance for any help///

Hi,

"Fetch first n rows only" in subquery is a new feature of db2 v.8.1. You are probably using some older version.

Please specify the version of db2 and fixpack level and the OS version and fixpack level.

Hope this helps,
Grofaty

I am running on both AIX and Win2000 DB2 UDB V7.?
lslpp on AIX does not tell me much, and the Win version
just says Version 7.

I have had to work around the problem by ignoring extra
rows while scanning the result.

Is there another way in DB2 to join a table and get just the
row with a maximum or minimum column? Lets say I have
a table of Departments, and a table of Employees which
have both a Department and a salary. How would I list
Departments and the highest paid Employee in each Department?

First you can find information about your db2 software with db2level command
You can use where exists, like where exists (select * ....)
Also I am giving you an example of using inline view

WITH
DINFO (DEPTNO, AVGSALARY, EMPCOUNT) AS
(SELECT OTHERS.WORKDEPT, AVG(OTHERS.SALARY), COUNT(*)
FROM EMPLOYEE OTHERS
GROUP BY OTHERS.WORKDEPT
),
DINFOMAX AS
(SELECT MAX(AVGSALARY) AS AVGMAX FROM DINFO)
SELECT THIS_EMP.EMPNO, THIS_EMP.SALARY,
DINFO.AVGSALARY, DINFO.EMPCOUNT, DINFOMAX.AVGMAX
FROM EMPLOYEE THIS_EMP, DINFO, DINFOMAX
WHERE THIS_EMP.JOB = 'SALESREP'
AND THIS_EMP.WORKDEPT = DINFO.DEPTNO

db2level returns the following on Win2000:

DB21085I Instance "DB2" uses DB2 code release "SQL07025" with level identifier
"03060105" and informational tokens "DB2 v7.1.0.68", "n020616" and "WR21311".

I am using a WHERE IN (SELECT ...) but the subselect needs
to return a MAX value of one row and that's what I can't get to work.
I don't need the MAX value of a column in a table, I need an
ID of of the row with the MAX value.

Your example below demonstrates the WITH clause I have not
used before, but does not solve the example problem I mention.
I want to list departments, and for each department row in the
result I want to have a column which contains the employee
who is the top paid for that department.

How's this? Can this help u?

Cheers, Bill


SELECT HOSTNAME
FROM NETWORKINTERFACES
WHERE NODEID=8713
AND NETWORKID=(
SELECT NETWORKID
FROM NETWORKINTERFACES AS A
LEFT OUTER JOIN NETWORKS AS B
ON B.NETWORKID = A.NETWORKID
WHERE NETINT.NODEID=8713
AND B.NETMASK = (
SELECT MIN(NETMASK)
FROM NETWORK C
WHERE C.NETWORKID = B.NETWORKID
)
)
;

Bill,
Your inner query (when corrected to run on my server)

SELECT B.NETWORKID
FROM MSTRESRC.NETWORKINTERFACES AS A
LEFT OUTER JOIN MSTRESRC.NETWORKS AS B
ON B.NETWORKID = A.NETWORKID
WHERE A.NODEID=8713
AND B.NETMASK = (
SELECT MIN(NETMASK)
FROM MSTRESRC.NETWORKS C
WHERE C.NETWORKID = B.NETWORKID
)

produces:

NETWORKID
1000
1001
1002

which is 3 results and breaks the outer query which must have one.

Node 8713 has three network interfaces, each of which has only one
network associated with it. Your MIN predicate is finding the minimum
from a set of ONE so it does not really do anything. I need the
minimum of all three interfaces, Here is a little more data:

NETWORKID NETMASK
1000 255.255.254.000
1001 255.255.255.000
1002 255.255.255.128

The inner query needs to return 1000, and without a FETCH FIRST ROW
ONLY, I don't know how to do it.

Can u try this out:

SELECT NETINT.HOSTNAME
FROM MSTRESRC.NETWORKINTERFACES AS NETINT
LEFT OUTER JOIN MSTRESRC.NETWORKS AS NETWORK ON NETWORK.NETWORKID=NETINT.NETWORKID
WHERE NETINT.NODEID=8713 AND NETWORK.NETMASK=
(SELECT MIN(NETWORK1.NETMASK)
FROM MSTRESRC.NETWORKINTERFACES AS NETINT1
LEFT OUTER JOIN MSTRESRC.NETWORKS AS NETWORK1 ON NETWORK1.NETWORKID=NETINT1.NETWORKID
WHERE NETINT1.NODEID=8713)

This works for the data in my example, but it does not insure that
only one hostname will be returned per node. If a node is on
two networks that have equal netmasks, both hostnames will
be returned. This is why I want the where clause in the outer
query to be on a networkid. My original inner query sorted by
netmask and in case of a tie, the FETCH FIRST ROW ONLY made
sure that only one was selected.

You can just add FETCH FIRST ROW ONLY on the outer query and change the inner query to a INNER JOIN ?

SELECT NETINT.HOSTNAME
FROM MSTRESRC.NETWORKINTERFACES AS NETINT
LEFT OUTER JOIN MSTRESRC.NETWORKS AS NETWORK ON NETWORK.NETWORKID=NETINT.NETWORKID
WHERE NETINT.NODEID=8713 AND NETWORK.NETMASK=
(SELECT MIN(NETWORK1.NETMASK)
FROM MSTRESRC.NETWORKINTERFACES AS NETINT1
JOIN MSTRESRC.NETWORKS AS NETWORK1 ON NETWORK1.NETWORKID=NETINT1.NETWORKID
WHERE NETINT1.NODEID=8713) FETCH FIRST ROW ONLY

And i think you can change the outer query to a INNER JOIN
cuz i dont see why you need to use LEFT OUTER JOIN.

The example I am giving has a predicate that grabs only one Node
with an ID of 8713. The actual production query grabs a bunch
of Nodes based on other predicates I am not including. My production
query is returning multiple rows per node and I have to post
process the results to take only the first one.

I am gathering other information also not shown here for the Node.
All I want is a subquery to grab the hostname on the biggest
network to populate one column of the result and not cause
mutiple rows per node.