Distinct and Row_NUM - SQL Server Forums

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

Distinct and Row_NUM

Author

Topic

BendJoe
Posting Yak Master

128 Posts

Posted - 2010-05-18 : 18:51:01

ALTER PROCEDURE [dbo].[sp_DMAP_SearchAccountsPayable]
(
@vendorName varchar(50)=null,
@startRowIndex int,
@maximumRows int
)
as
begin
Select DocumentID,
Vendor
from(
Select
DocumentID,
Vendor,
ROW_NUMBER() over (order By distinctRows.DocumentID desc) as RowRank
from
(
SELECT distinct
d.DocumentID as DocumentID,
v.COMPANY_NAME AS Vendor

FROM dbo.DMDocument AS d INNER JOIN
dbo.CONTACT v on d.contactid = v.CONTACT_ID

where
(v.COMPANY_NAME like '%'+@vendorName+'%' or @vendorName is null)
) as distinctRows ) as resultwithRowRank

where RowRank > @startRowIndex AND RowRank <= (@startRowIndex + @maximumRows)
end

I have a procedure like this.
The problem I have is because of the row_num column applying the distinct on that result set is pointless. So I am creating three select queries the inner most one gets the distinct values and then the middle query adds the row numbers and the third outer query filters based on the start row and row size.
Is there a way I can get the same thing done with two select queries rather than the three which I have above.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-19 : 12:08:33

Can you try this.

Select DocumentID,Vendor
from
(
SELECT
DENSE_RANK() OVER(ORDER BY d.DocumentID DESC) AS RowRank
, d.DocumentID as DocumentID
, v.COMPANY_NAME AS Vendor
FROM dbo.DMDocument AS d 
INNER JOIN dbo.CONTACT v on d.contactid = v.CONTACT_ID 
WHERE (v.COMPANY_NAME like '%'+@vendorName+'%' or @vendorName is null) 
) t 
where RowRank > @startRowIndex AND RowRank <= (@startRowIndex + @maximumRows)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources