?
Solved

SQL duplicates - Distinct fails when using ROW_NUMBER()

Posted on 2011-10-31
3
Medium Priority
?
337 Views
Last Modified: 2012-05-12
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
Comment
Question by:awalkinthepark
3 Comments
 
LVL 39

Accepted Solution

by:
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

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

Author Closing Comment

by:awalkinthepark
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

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

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…

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question