help needed sql multi columns id's

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)

help needed sql multi columns id's

Author

Topic

geoffsmiths
Starting Member

7 Posts

Posted - 2010-05-26 : 10:16:03

Hi there,

I've got no more hair because of this single frustrating issue:
I've got the following:
ID | DATA_ID | URL | STORE_ID
-----------------------------
1 | 24 | bla | 10
2 | 11 | lol | 11
3 | 24 | Jak | 12
4 | 59 | iop | 5

I want as result:
ID | DATA_ID | URL | STORE_ID
-----------------------------
1 | 24 | bla | 10
3 | 24 | Jak | 12

How do I manage this in an SQL query?

Please help!!

My regards,

Geoff

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-26 : 10:34:24

select t1.ID, t1.DATA_ID, t1.URL, t1.STORE_ID
from your_table t1
join (select DATA_ID from your_table group by DATA_ID having count(*) > 1)dt
on dt.DATA_ID = t1.DATA_ID
order by t1.DATA_ID, t1.ID

edit: order by was wrong

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-05-26 : 10:36:05

select t1.* from table as t1 inner join
(
select data_id from table group by data_id having count(*)>1
) as t2
on t1.data_id=t2.data_id

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-05-26 : 10:37:01