Reputation: 1164
I am not entirely sure how to word what I am looking for which is making searching difficult. But what I am trying to do is return a single record for every distinct column match
Table Structure:
ItemHolderId Name
------------ --------------------------------------------------
1 Holder A
2 Holder B
ItemId Data ItemHolderId
----------- -------------------------------------------------- ------------
1 Item A 1
2 Item B 1
3 Item C 1
4 Item D 1
5 Item E 2
6 Item F 2
7 Item G 2
I am looking to select a single item for each item holder id. So it would only select Item A and Item E for example. Order doesn't matter just one record for each matched column. I hope I am explaining this in a sensible manner.
Thanks for your time.
Upvotes: 1
Views: 3625
Reputation: 23
I think you can use group by function for itemholderid column as well, it will help I guess.
select
rownum itemId,
tmp.name ,
tmp.itemholderid
from
(select
min(t.name) name,
t.itemholderid
from
table_name t
group by t.itemholderid
)tmp;
Upvotes: 0
Reputation: 9481
You can use row_number()
to partition the data by the user, and then grab each record with a '1' as the [rank]
. This way you can control the partition and sort of the data to control which record is given the value of '1', and thus returned...
/* Setup tables for query */
declare @tbl1 table (ItemHolderId int, Name varchar(32))
declare @tbl2 table (ItemId int, Data varchar(32), ItemHolderId int)
insert into @tbl1 values (1, 'Holder A'), (2, 'Holder B')
insert into @tbl2 values (1, 'Item A', 1), (2, 'Item B', 1), (3, 'Item C', 1), (4, 'Item D', 1)
insert into @tbl2 values (5, 'Item E', 2), (6, 'Item F', 2), (7, 'Item G', 2)
/* Select data */
select t2.*, row_number() over (partition by t1.ItemHolderId order by t2.ItemHolderId) as [rank]
into #temp
from @tbl1 t1 inner join @tbl2 t2 on t1.ItemHolderId = t2.ItemHolderId
select ItemId, Data, ItemHolderId from #temp where [rank] = 1
drop table #temp
Upvotes: 2
Reputation: 755541
One approach would be to use a CTE (Common Table Expression) if you're on SQL Server 2005 and newer (you aren't specific enough in that regard).
With this CTE, you can partition your data by some criteria - i.e. your ItemHolderId
- and have SQL Server number all your rows starting at 1 for each of those partitions, ordered by some other criteria (you need some criteria - which one you use is up to you).
So try something like this:
;WITH PartitionedComponents AS
(
SELECT
ih.ItemHolderID, ih.Name, d.Data,
ROW_NUMBER() OVER(PARTITION BY ih.ItemHolderID ORDER BY d.Data DESC) AS 'RowNum'
FROM
dbo.ItemHolder ih
INNER JOIN
dbo.ItemHolderData d ON ih.ItemHolderID = d.ItemHolderID
WHERE
ComponentId IN (.....)
AND ConsoleTimeStamp <= (threshold)
)
SELECT
ItemHolderID, Name, Data
FROM
PartitionedComponents
WHERE
RowNum = 1
Here, I am selecting only the last two entries for each "partition" (i.e. for each ItemHolderId
) - ordered in a descending fashion by the "Data" column.
Does that approach what you're looking for??
Upvotes: 2