SQL duplicates - Distinct fails when using ROW_NUMBER()

Posted on 2011-10-31
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

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

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

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 =
where Table_1.description= 'data1'

Question by:awalkinthepark
    LVL 39

    Accepted Solution

    if you need both distinct and row_number use it like this

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

    Assisted Solution

    by:Guy Hengel [angelIII / a3]

    Author Closing Comment

    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

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
    Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    733 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

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now