Database Programming: Mixing TOP With UNION

I'd like to share yet another interesting conversation from an internal discussion list.

Michael sent a query that was producing inconsistent results:

select

TOP 35 1 AS [PRECEDANCE], W03a.CRDATTIM, W03a.RECORDCD, W03a.CRNODE, W03a.PRTY + TM.TEAMPRTY AS [TOTAL PRIORITY]
from WL4U999S TM,
WL3U999S WRKa,
W03U999S W03a
where W03a.LOCKSTAT = ' '
and rtrim(W03a.END_QUEUE_FLG) = 'N'
and rtrim(W03a.SUSPFLAG)= 'N'
and rtrim(W03a.INXFLD03) = rtrim(TM.TEAM)
and rtrim(TM.USERID) = 'AWDSETUP'
and rtrim(W03a.QUEUECD) = rtrim(TM.QUEUECD)
and rtrim(W03a.WRKTYPE) = rtrim(WRKa.WRKTYPE)
and rtrim(WRKa.USERID) = rtrim(TM.USERID)
and rtrim(W03a.UNITCD) = rtrim(WRKa.UNITCD)
and rtrim(W03a.QUEUECD) = rtrim(WRKa.QUEUECD)

union

select

top 35 2 AS [PRECEDANCE], W03b.CRDATTIM, W03b.RECORDCD, W03b.CRNODE, W03b.PRTY
AS [TOTAL PRIORITY]
from WL3U999S WRKb,
W03U999S W03b
where W03b.LOCKSTAT = ' '
and W03b.END_QUEUE_FLG = 'N'
and W03b.SUSPFLAG = 'N'
and WRKb.USERID = 'AWDSETUP'
and W03b.UNITCD = WRKb.UNITCD
and W03b.WRKTYPE = WRKb.WRKTYPE
and W03b.QUEUECD = WRKb.QUEUECD
and W03b.QUEUECD not in
( select W30.QUEUECD
from W30U999S W30 where W03b.QUEUECD = W30.QUEUECD)
order by 5 desc, 1, 2
option(force order);

Michael's intent was to produce 70 rows from this query, but he was only getting 35 back. The issue is that the TOP from the first query is running over the entire UNIONed resultset.

Fortunately, we can resolve this issue fairly easily. All we need to do is place parentheses around each subquery and give each subquery an alias:

select PRECEDANCE, CRDATTIM, RECORDCD, CRNODE, [TOTAL PRIORITY]

FROM (

select TOP 35 1 AS [PRECEDANCE], W03a.CRDATTIM, W03a.RECORDCD, W03a.CRNODE, W03a.PRTY + TM.TEAMPRTY AS [TOTAL PRIORITY]

from WL4U999S TM,

WL3U999S WRKa,

W03U999S W03a

where W03a.LOCKSTAT = ' '

and rtrim(W03a.END_QUEUE_FLG) = 'N'

and rtrim(W03a.SUSPFLAG)= 'N'

and rtrim(W03a.INXFLD03) = rtrim(TM.TEAM)

and rtrim(TM.USERID) = 'AWDSETUP'

and rtrim(W03a.QUEUECD) = rtrim(TM.QUEUECD)

and rtrim(W03a.WRKTYPE) = rtrim(WRKa.WRKTYPE)

and rtrim(WRKa.USERID) = rtrim(TM.USERID)

and rtrim(W03a.UNITCD) = rtrim(WRKa.UNITCD)

and rtrim(W03a.QUEUECD) = rtrim(WRKa.QUEUECD)

) a

union

select PRECEDANCE, CRDATTIM, RECORDCD, CRNODE, [TOTAL PRIORITY]

FROM (

select top 35 2 AS [PRECEDANCE], W03b.CRDATTIM, W03b.RECORDCD, W03b.CRNODE, W03b.PRTY AS [TOTAL PRIORITY]

from WL3U999S WRKb,

W03U999S W03b

where W03b.LOCKSTAT = ' '

and W03b.END_QUEUE_FLG = 'N'

and W03b.SUSPFLAG = 'N'

and WRKb.USERID = 'AWDSETUP'

and W03b.UNITCD = WRKb.UNITCD

and W03b.WRKTYPE = WRKb.WRKTYPE

and W03b.QUEUECD = WRKb.QUEUECD

and W03b.QUEUECD not in

( select W30.QUEUECD

from W30U999S W30 where W03b.QUEUECD = W30.QUEUECD)

order by 5 desc, 1, 2

option(force order)

) b

The revised query returns a 70 record resultset, as intended.

     -wp