Outer Query returns more results than Subquery

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Outer Query returns more results than Subquery

Author

Topic

MangoSkin
Starting Member

22 Posts

Posted - 2011-01-20 : 12:35:13

In the following query the inner select has only 4 results but the outer select has 21 results.
Select
DocumentID,
DocumentTitle,
GST,
SubTotal
FROM
(
Select distinct v.DocumentID ,
v.DocumentTitle ,
v.GST ,
v.SubTotal,
ROW_NUMBER() OVER (ORDER BY InvoiceDate DESC) AS RowRank

from
DMViewUnverifiedInvoices v left outer join DMAP_ACC_OTHER_INVOICES_DIST i on
v.invoicenumber = i.invoice_num and v.VendorId =i.VENDOR_NUM
left outer join lkpProject p on i.project_number=p.projectid
left outer join tblunit u on u.accountingunitnumber=i.unit_number Where (v.invoicenumber like '%01172011%') and 1=1) ResultWithRowNumber
WHERE RowRank > 0 AND RowRank <= (0 + 100) ;

What is happening in this?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-01-20 : 12:48:35

Are you sure? The distinct is applied after the row_number so won't actually do anything

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

MangoSkin
Starting Member

22 Posts

Posted - 2011-01-20 : 13:20:14

quote:
Originally posted by nigelrivett

Are you sure? The distinct is applied after the row_number so won't actually do anything

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Here is more
Select distinct v.DocumentID ,
v.DocumentTitle ,
v.GST ,
v.SubTotal,
ROW_NUMBER() OVER (ORDER BY InvoiceDate DESC) AS RowRank

from
DMViewUnverifiedInvoices v left outer join DMAP_ACC_OTHER_INVOICES_DIST i on
v.invoicenumber = i.invoice_num and v.VendorId =i.VENDOR_NUM
left outer join lkpProject p on i.project_number=p.projectid
left outer join tblunit u on u.accountingunitnumber=i.unit_number Where (v.invoicenumber like '%01172011%') and 1=1

gives four records.
and
Select v.DocumentID ,
v.DocumentTitle ,
v.GST ,
v.SubTotal,
ROW_NUMBER() OVER (ORDER BY InvoiceDate DESC) AS RowRank

from
DMViewUnverifiedInvoices v left outer join DMAP_ACC_OTHER_INVOICES_DIST i on
v.invoicenumber = i.invoice_num and v.VendorId =i.VENDOR_NUM
left outer join lkpProject p on i.project_number=p.projectid
left outer join tblunit u on u.accountingunitnumber=i.unit_number Where (v.invoicenumber like '%01172011%') and 1=1
gives 21 records.
I am expecting same number of reecords as row_number nullifies distinct.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources