Table 1- GroupedItems
int SharedId
int GroupId
Table 2- SharedItems
int SharedId
int ItemId
In Table 1 SharedId is unique
In Table 2 ItemId is unique
Table 1 represents all of a group's shared Items
Table 2 matches a group of Items to a common sharedId
Example
Table 1
1 - 1
2 - 1
3 - 2
Table 2
1 - 1
1 - 2
2 - 3
2 - 4
3 - 5
What I am trying to do is have a select command where you would pass in a GroupId and it would select just one Item from table 2 for each SharedId
If you were to specify Group 1 you would Get
ItemIds 1 and 3 from Table 2, although its not needed that the results are the top items.
You can use something like this query in 2005 to get a unique set of items:
create table SharedItems
(
sharedId int,
itemId int primary key
)
insert into SharedItems
select 1,1
union all
select 1,2
union all
select 2,3
union all
select 2,4
union all
select 3,5
go
select sharedId, itemId
from ( select sharedId, itemId, row_number() over (partition by sharedId order by itemId) as rowNumber
from SharedItems ) as numbered
where numbered.rowNumber = 1
|||Thank you for your help.
This is exactley what I needed.
|||
More straight forward way to write the query is to do below:
select s1.sharedId, s1.itemId
from SharedItems as s1
where s1.itemId = (select min(s2.itemId) from SharedItems as s2 where s2.sharedId = s1.sharedId)
And depending on your indexes/data this might give better performance than the ROW_NUMBER approach. For this particular example, the above approach will be twice as fast as the ROW_NUMBER approach. And if you have an index on say sharedId then the performance difference will be more significant.
|||True enough. I am always just a bit concerned about duplicate and ordering so I just defaulted to doing it that way. I should have thought about this better way since there is a single value to correlate with. Thanks!|||Thanks to both of you.This approach looks a little more like what I was expecting, but I'm glad I was able to learn about the row number
