unique row needed, - 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)

unique row needed,

Author

Topic

geoffsmiths
Starting Member

7 Posts

Posted - 2010-05-26 : 10:45:33

Hello SQL experts!

I've got the following issue:

ID | DATA_ID | URL | STORE_ID
-----------------------------
1 | 24 | bla | 10
2 | 11 | lol | 11
3 | 24 | Jak | 12
4 | 11 | iop | 5

And This is what I want: I want for each data_id a the lowest store_id. I do need the ID, Data_id, URL and Store_ID.

ID | DATA_ID | URL | STORE_ID
-----------------------------
1 | 24 | bla | 10
4 | 11 | iop | 5

Once more I need your help!

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-05-26 : 11:00:29

Try this:

Declare @Data table
(
Id int,
Data_id int,
URL varchar(50),
Store_id int)

Insert into @Data
Select 1 , 24 , 'bla' , 10 union
Select 2 , 11 , 'lol' , 11 union
Select 3 , 24 , 'Jak' , 12 union
Select 4 , 11 , 'iop' , 5

Select Id, Data_id, url, Store_id from
@Data D1 where store_id = (Select min(store_id) from @Data D2
where D1.DATA_ID = D2.DATA_ID)
order by id

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-26 : 11:01:34

select ID,DATA_ID,URL,STORE_ID
from
(select rownumber() over (partition by DATA_ID order by STORE_ID ASC) as rownum,* from Your_table)dt
where rownum=1

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

geoffsmiths
Starting Member

7 Posts

Posted - 2010-05-26 : 11:14:48

&WEBFRED -> That worked great! Just simple and great SQL.
Thank you again!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-26 : 11:17:20

welcome