StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PODuplicate results returned from query when distinct is used
text
Body
copied!<p>On a current project at I am needing to do some pagination of results returned from SQL. I have hit a corner case in which the query can accept identifiers as part of the where clause, normally this isn't an issue but in one case we have a single identifier being passed up that has a one to many relationship with one of the tables that the query joins on and it is returning multiple rows in the results. That issue was fixed by introducing a distinct to the query. The following is the query which returns the correct result of one row (all table/field names have been changed of course):</p> <pre><code>select distinct [item_table].[item_id] , row_number() over (order by [item_table].[pub_date] desc, [item_table].[item_id]) as [row_num] from [item_table] join [OneToOneRelationShip] on [OneToOneRelationShip].[other_id] = [item_table].[other_id] left join [OneToNoneOrManyRelationship] on [OneToNoneOrManyRelationship].[item_id] = [item_table].[item_id] where [item_table].[pub_item_web] = 1 and [item_table].[live_item] = 1 and [item_table].[item_id] in (1404309) </code></pre> <p>However when I introduce pagination into the query I am finding that it is now returning multiple rows when it should be only be returning one. The method I am using for pagination is as follows:</p> <pre><code>select [item_id] from ( select distinct [item_table].[item_id] , row_number() over (order by [item_table].[pub_date] desc, [item_table].[item_id]) as [row_num] from [item_table] join [OneToOneRelationShip] on [OneToOneRelationShip].[other_id] = [item_table].[other_id] left join [OneToNoneOrManyRelationship] on [OneToNoneOrManyRelationship].[item_id] = [item_table].[item_id] where [item_table].[pub_item_web] = 1 and [item_table].[live_item] = 1 and [item_table].[item_id] in (1404309) ) as [items] where [items].[row_num] between 0 and 100 </code></pre> <p>I worry that adding a distinct to the outer query will cause an incorrect number of results to be returned and I am unsure of how else to fix this issue. The database I am querying is MS SQL Server 2008.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload