CODEWITHSUNDEEP
sql-server
Brian
Brian

Reputation: 1164

SQL - return one row for every match

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

Answers (3)

Rajesh Selvan
Rajesh Selvan

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

James L.
James L.

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

marc_s
marc_s

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

Related Questions