distinct and top in the same query!!

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 and top in the same query!!

Author

Topic

agarwasa2008
Posting Yak Master

109 Posts

Posted - 2009-08-25 : 18:47:47

select top 20 distinct a.col1, b.col1, b.col2 , b.col3 , b.col4, b.col5
from dbo.table1 a
Right Outer Join dbo.table2 b
on b.col1 = a.col1
where a.col1 like '%Bx8%'
order by a.col2 desc

This is giving me a lot of duplicates. So I need to use distinct. Also I need only the top 20 records.

Please help!!

SA

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-08-25 : 18:51:31

This is one way:

select top 20 * from
(select distinct a.col1, b.col1, b.col2 , b.col3 , b.col4, b.col5
from dbo.table1 a
Right Outer Join dbo.table2 b
on b.col1 = a.col1
where a.col1 like '%Bx8%')dt
order by col2 desc

No, you're never too old to Yak'n'Roll if you're too young to die.

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2009-08-25 : 18:54:34

try this one



select top 20 b.col1, b.col2 , b.col3 , b.col4, b.col5
from dbo.table2 b
where exists(select * from dbo.table1 a
			where a.col1=b.col2 and a.col1 like '%Bx8%' )
order by b.col2 desc

agarwasa2008
Posting Yak Master

109 Posts

Posted - 2009-08-25 : 19:01:43

I did exactly what you did and I get the following error. Is it working for you.

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "a.col1" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "a.col10" could not be bound.

SA

agarwasa2008
Posting Yak Master

109 Posts

Posted - 2009-08-25 : 19:12:46

Here's the actual query:
____________________________

select top 20 a.id, b.change_user, b.change_date
from dbo.table2 b
where exists(select * from dbo.view_all_prd_info a
where a.id=b.id and a.id like '%Bx8%' )
order by a.mtrl_trim_id desc

SA

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2009-08-25 : 19:21:00

[code]

select top 20 a.id, D.change_user, D.change_date
from
(select b.id
from dbo.table2 b
where exists(select * from dbo.view_all_prd_info a
where a.id=b.id and a.id like '%Bx8%' )
) AS D INNER JOIN dbo.view_all_prd_info a ON a.id=D.id
order by a.mtrl_trim_id desc

[/code]

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-08-26 : 00:35:39

quote:

Originally posted by ms65g




select top 20 a.id, D.change_user, D.change_date
from
(select b.id
from dbo.table2 b
where exists(select * from dbo.view_all_prd_info a
where a.id=b.id and a.id like '%Bx8%' )
) AS D INNER JOIN dbo.view_all_prd_info a ON a.id=D.id
order by a.mtrl_trim_id desc

u will get error change_user,change_date is not there in the 'D' Derived table Check it once.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources