row_number() Help!

I have a

searching SP that uses the row_number() function to help out with

paging. But for some weird reason Im getting duplicate results when I

run the query. But if I take out the row_number() funciton I get normal

results. Here is what I have got, please help me with getting this

solved, im tearing my hair out!

WITH SearchTable AS
(
select distinct
st.id,
st.title,
st.sub_title,
st.synopsis,
st.short_code,
ROW_NUMBER() OVER (ORDER BY st.title asc) AS RowNumber

from stock as st left join
Keywords on Keywords.stock_id = st.id left join
OnlinePreviews opLow ON opLow.stock_id = st.id and opLow.[type] = 1 left join
OnlinePreviews opHigh ON opHigh.stock_id = st.id and opHigh.[type] = 2 left join
TeachersNotes tn ON tn.stock_id = st.id inner join
Stock_Subjects ss ON ss.stock_id = st.id inner join
Subjects sub ON sub.id = ss.subject_id
)

select
id,
title,
sub_title,
synopsis,
short_code,
RowNumber
from SearchTable st
Where
RowNumber between ((@.page - 1) * @.results) AND (@.page * @.results)
Order by short_code

So

thats the SQL, but keep in mind the entire SQL works fine if we take

all all references to the row_number() function. If I leave the

row_function() in then I can work out that it is my inner joins thats

the problem, if I slowly remove the inner joins and keep in the

row_number(), I figured out that the joins on the Stock_Subjects table

is the problem.

Why would this give different results when I use the row_number() funciton?

Thanks heaps to who ever solves this, I just can't figure it out!You will have to post a repro script that demonstrates the problem. ROW_NUMBER will generate unique numbers per row even if there are duplicates.|||As it turns out you are on the right track. I was selecting distinct so as try to eliminate the duplicate rows made from some joins in my query. But I also really needed to select the row_number() which, as you mentioned, was giving back a unique row number so distinct was effectivly useless.