Distinct not working - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Distinct not working

Author

Topic

evvo
Starting Member

16 Posts

Posted - 2008-08-18 : 09:43:34

Im investigating an issue on a website whereby a page is listing the same item multiple times.

Looking at the sql i see a SELECT DISTINCT statement wrapped around an inner query function that is pulling back DISTINCT row numbers using the ROW_NUMBER function.

Now curiously removing the outer DISTINCT makes no difference to the results returned so is this because the SQL is seeing them all as unique because the row number is present. Im presumimng this is the cause.

How would be the best way to address this? All other data pulled from the inner query is the same apart from row number so i would be expecting only ONE result returned if i could discount this column.

I can see the row num has been returned as it is used for some paging functionality populating 10 per page, so i cant discount it entirely.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-18 : 09:53:49

[code]
select *
from
(
select *, row_no = row_number() over (order by ..)
from
(
select distinct cola, colb, colc, ..
from yourtable
)a
) b
where row_no <= 10
[/code]

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-18 : 09:55:46

Can you post the current. its very difficult without seeing what to suggest as we dont know what your intended result is. you could also give some sample data and explain your expected results.

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-08-18 : 09:57:57

quote:
Originally posted by evvo

is this because the SQL is seeing them all as unique because the row number is present.

Of course.

quote:
Originally posted by evvoHow would be the best way to address this? All other data pulled from the inner query is the same apart from row number so i would be expecting only ONE result returned if i could discount this column.

Well what you say is not what you should expect. Distinct works on all the rows selected. You probably want

select * from
(
select *, select row_number() ...... as rn from
(select distinct a,b,c from t) x
) y
where rn ....

In my experience though, distinct is wrong most of the time and oftern covers up a malformed query, especially when there is a join involved.

evvo
Starting Member

16 Posts

Posted - 2008-08-18 : 10:22:00

ok thanks ill look into these.

i did find another solution on the web just now :

WITH DUPLICATE(ROW,GROUPROW,ID,FNAME,LNAME)
AS
(
SELECT ROW,GROUPROW= CASE WHEN ID=ID
THEN
(SELECT COUNT(*) FROM (SELECT ROW_NUMBER() OVER (ORDER BY ID) AS ROW,
ID,LNAME,FNAME FROM MYTABLE
) AS A WHERE A.ID=B.ID AND
A.ROW<B.ROW)+1 END,ID,FNAME,LNAME FROM (SELECT ROW_NUMBER() OVER (ORDER
BY ID) AS ROW,
ID,LNAME,FNAME FROM MYTABLE
)AS B
)
DELETE FROM DUPLICATE WHERE GROUPROW<>1
SELECT * FROM MYTABLE ORDER BY ID

Run with my parameters in the query window, this does work, so will try and incorporate into my code.

thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources