Solved

# SQL duplicates - Distinct fails when using ROW_NUMBER()

Posted on 2011-10-31
Medium Priority
337 Views
This example will show the problem.
Create the 2 tables, run the insert then the problem query to see it.

"Should" return one distinct row.
Looking for a way around it.
Looks like distinct is applied after the ROW_NUMBERS() are
made, so, distinct fails because of the row numbers.

How can you get around this?
We need both the row_number() and distinct

CREATE TABLE [dbo].[Table_1](
[id] [int] IDENTITY(1,1) NOT NULL,
[description] [nchar](10) NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[Table_2](
[id] [int] IDENTITY(1,1) NOT NULL,
[Table_1_ID] [int] NULL,
[description] [nchar](10) NULL
) ON [PRIMARY]

insert into table_1(description)values('data1')
insert into table_1(description)values('data2')

insert into table_2([Table_1_ID],description)values(1,'A')
insert into table_2([Table_1_ID],description)values(1,'B')
insert into table_2([Table_1_ID],description)values(1,'C')

Run this query, which should return the one row from
Table_1,

select distinct Table_1.description
,ROW_NUMBER() over (ORDER BY Table_1.description) as rowid
from Table_1  join Table_2 on Table_2.table_1_id = Table_1.id
where Table_1.description= 'data1'

0
Question by:awalkinthepark

LVL 39

Accepted Solution

appari earned 1000 total points
ID: 37060224
if you need both distinct and row_number use it like this

Select *, ROW_NUMBER() over (ORDER BY description) as rowid
From(
select distinct Table_1.description
from Table_1  join Table_2 on Table_2.table_1_id = Table_1.id
where Table_1.description= 'data1') as A
0

LVL 143

Assisted Solution

Guy Hengel [angelIII / a3] earned 1000 total points
ID: 37061876
0

Author Closing Comment

ID: 37067152
Excellent article from angelIl
Obviously more all encompassing.
appari - got there first and is directly to the point.
Wish I could award 500 each.
Thanks guys
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
###### Suggested Courses
Course of the Month14 days, 2 hours left to enroll