Solved

Grouping records by multiple fields in SQL query

Posted on 2011-02-14
10
209 Views
Last Modified: 2012-08-13
Dear Experts,

I'm having some problems with a SQL stored procedure which I hoped somebody might be able to shed some light on for me.

I wish to return all the rows in this query but only one item for each Type_ID and Item_ID.

i.e. if there are multiple entries with the same Type_ID *and* Item_ID, only return the first.

I've included my table (temporary for the time being) and query below.

Thanks in advance for any help you can give me.

Nick
SELECT *, ROW_NUMBER() OVER (ORDER BY Due_Date DESC) as Row  FROM #OutputTable WHERE ISNULL(IsCompleted,0)=0 AND [TYPE_ID] IN (4,5) ORDER BY Due_Date DESC

CREATE TABLE #OutputTable  
(
	Task_ID int,
	Item_ID int,
	[Type_ID] int,
	Task_Title varchar(500), 
	Priority_ID int, 
	Due_Date datetime, 
	TaskType_ID int, 
	TaskRef_ID int, 
	Tags varchar(500), 
	Creator varchar(200), 
	Created datetime, 
	LastUpdated datetime, 
	Ident int, 
	IsCompleted bit, 
	Duration_ID int, 
	OwnerType_ID int, 
	OwnerTypeItem_ID varchar(200), 
	Company_ID int, 
	TaskType_Name varchar(200), 
	Priority_Name varchar(200),
	LinkedEntity varchar(200),
	AppointmentStatus_ID int,
	IsFlagged bit
)

Open in new window

0
Comment
Question by:nkewney
  • 5
  • 4
10 Comments
 
LVL 11

Expert Comment

by:rajvja
Comment Utility
SELECT * FROM
(
SELECT *, ROW_NUMBER() OVER (PARTITION BY Item_id ORDER BY Due_Date DESC) as Row  FROM #OutputTable WHERE ISNULL(IsCompleted,0)=0 AND [TYPE_ID] IN (4,5) ORDER BY Due_Date DESC
) tab where Row = 1
0
 
LVL 10

Expert Comment

by:John Claes
Comment Utility
Nick,

is there an Unique Key in the table? I presume it's Task_id

you can always do something like this :


Select * from #OutputTable
where Task_ID in (select min(Task_ID) from #OutputTable group by [Type_ID], Item_id )
0
 
LVL 1

Author Comment

by:nkewney
Comment Utility
Hi rajvja,

THanks for the answer.

Could you explain your solution further?

A sample output might be

Item_ID, Type_ID, Task_Title
1, 1, Item 1
1, 1, Item 2
2, 2, Item 1

In this case, I'd only want

1, 1, Item 1
2, 2, Item 1

I'd also want the row numbers to remain sequential.  Would this achieve this?

Nick
0
 
LVL 10

Expert Comment

by:John Claes
Comment Utility
I'm sorry,

It should be this one (I overlooked you're Query)

SELECT * FROM
(
SELECT *, ROW_NUMBER() OVER (PARTITION BY [Type_ID], Item_id ORDER BY Due_Date DESC) as Row  FROM #OutputTable WHERE ISNULL(IsCompleted,0)=0 AND [TYPE_ID] IN (4,5) --ORDER BY Due_Date DESC
) tab where Row = 1
0
 
LVL 10

Expert Comment

by:John Claes
Comment Utility
Sollution explaination :

We take you're Where clausele so we have only the data you want.

then we add the OVER partition part so we take the parts of the Table together wherein the Type_Id and Item_Id are identical (Ordered by the date)

The where row=1 is then used to only take the first row for each occurance of the Type_Id and Item_Id combination.



changes against the answer of rajvja :

Escaped the " ORDER BY Due_Date DESC" after the where clausule : Doens't work
Added "[Type_ID],"  into the partition clause because you want to combine those 2 fields.

regards
poor beggar
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 1

Author Comment

by:nkewney
Comment Utility
Thanks poor_beggar.

Row is used by my system to help navigate the records, therefore it needs to be sequential through the records:

as in...
Row, Item_ID, Type_ID, Title
======================
1, 1, 1, Item 1
2, 2, 2, Item 1
3, 43, 53, Item 23

Does this make sense?

0
 
LVL 10

Expert Comment

by:John Claes
Comment Utility
nkewney,

the Row is only the rownumber that is given by ROW_NUMBER()  function.
This adding of a value is set for every partition and sequencing the records by due_date
and resetting the numbering for each occurance of the  [Type_ID], Item_id  combination.

the Row-Field will always be 1 (See last where)

On what field do you want to make them sequencial?


SELECT * FROM
(
SELECT *, ROW_NUMBER() OVER (PARTITION BY [Type_ID], Item_id ORDER BY Due_Date DESC) as Row  FROM #OutputTable WHERE ISNULL(IsCompleted,0)=0 AND [TYPE_ID] IN (4,5)) tab where Row = 1


run the example beneath for a better view of the answers result.

 
CREATE TABLE #OutputTable  
(
	Task_ID int,
	Item_ID int,
	[Type_ID] int,
	Task_Title varchar(500), 
	Priority_ID int, 
	Due_Date datetime, 
	TaskType_ID int, 
	TaskRef_ID int, 
	Tags varchar(500), 
	Creator varchar(200), 
	Created datetime, 
	LastUpdated datetime, 
	Ident int, 
	IsCompleted bit, 
	Duration_ID int, 
	OwnerType_ID int, 
	OwnerTypeItem_ID varchar(200), 
	Company_ID int, 
	TaskType_Name varchar(200), 
	Priority_Name varchar(200),
	LinkedEntity varchar(200),
	AppointmentStatus_ID int,
	IsFlagged bit
) 
insert into #OutputTable (Task_ID, [Type_ID], Item_id, IsCompleted,Due_Date  ) values (1,4,1,0,getdate()-1)
insert into #OutputTable (Task_ID, [Type_ID], Item_id, IsCompleted,Due_Date ) values (2,5,1,0,getdate()-2)
insert into #OutputTable (Task_ID, [Type_ID], Item_id, IsCompleted,Due_Date ) values (3,4,2,0,getdate()-3)
insert into #OutputTable (Task_ID, [Type_ID], Item_id, IsCompleted,Due_Date) values (4,4,1,0,getdate()-4 )
insert into #OutputTable (Task_ID, [Type_ID], Item_id, IsCompleted,Due_Date ) values (5,5,2,0,getdate())
insert into #OutputTable (Task_ID, [Type_ID], Item_id, IsCompleted,Due_Date ) values (6,5,1,0,getdate()+1)
insert into #OutputTable (Task_ID, [Type_ID], Item_id, IsCompleted,Due_Date ) values (7,4,2,0,getdate()+2)
insert into #OutputTable (Task_ID, [Type_ID], Item_id, IsCompleted,Due_Date) values (8,4,1,0,getdate()+3 )


SELECT * FROM
(
SELECT *, ROW_NUMBER() OVER (PARTITION BY [Type_ID], Item_id ORDER BY Due_Date DESC) as Row  FROM #OutputTable WHERE ISNULL(IsCompleted,0)=0 AND [TYPE_ID] IN (4,5)
) tab where Row = 1 order by task_id


drop table #OutputTable

Open in new window

0
 
LVL 1

Author Comment

by:nkewney
Comment Utility
This is perfect apart from the "Row" column.

It needs to be sequential for all results returned.

In this example, it would be 1, 2, 3, 4 as 4 results are returned by the query.

Nick
0
 
LVL 10

Accepted Solution

by:
John Claes earned 500 total points
Comment Utility
Nick:  

the row column is used for Over  clause.
Do you wan't an extra column with a Index in the Result ?


I've sorted combination by DueDate so I've added the numbering for the records also upon the due_date


changed Query ==> added Row_Index for EveryRow
Only using the Orderby option of the Over clause to order the return recordset and add the RowIndex

SELECT *,ROW_NUMBER() OVER (ORDER BY Due_Date) AS Row_Index  FROM
(
SELECT *, ROW_NUMBER() OVER (PARTITION BY [Type_ID], Item_id ORDER BY Due_Date DESC) as Row  FROM #OutputTable WHERE ISNULL(IsCompleted,0)=0 AND [TYPE_ID] IN (4,5)
) tab where Row = 1 order by task_id


example Code beneath :

 
CREATE TABLE #OutputTable  
(
	Task_ID int,
	Item_ID int,
	[Type_ID] int,
	Task_Title varchar(500), 
	Priority_ID int, 
	Due_Date datetime, 
	TaskType_ID int, 
	TaskRef_ID int, 
	Tags varchar(500), 
	Creator varchar(200), 
	Created datetime, 
	LastUpdated datetime, 
	Ident int, 
	IsCompleted bit, 
	Duration_ID int, 
	OwnerType_ID int, 
	OwnerTypeItem_ID varchar(200), 
	Company_ID int, 
	TaskType_Name varchar(200), 
	Priority_Name varchar(200),
	LinkedEntity varchar(200),
	AppointmentStatus_ID int,
	IsFlagged bit
) 
insert into #OutputTable (Task_ID, [Type_ID], Item_id, IsCompleted,Due_Date  ) values (1,4,1,0,getdate()-1)
insert into #OutputTable (Task_ID, [Type_ID], Item_id, IsCompleted,Due_Date ) values (2,5,1,0,getdate()-2)
insert into #OutputTable (Task_ID, [Type_ID], Item_id, IsCompleted,Due_Date ) values (3,4,2,0,getdate()-3)
insert into #OutputTable (Task_ID, [Type_ID], Item_id, IsCompleted,Due_Date) values (4,4,1,0,getdate()-4 )
insert into #OutputTable (Task_ID, [Type_ID], Item_id, IsCompleted,Due_Date ) values (5,5,2,0,getdate())
insert into #OutputTable (Task_ID, [Type_ID], Item_id, IsCompleted,Due_Date ) values (6,5,1,0,getdate()+1)
insert into #OutputTable (Task_ID, [Type_ID], Item_id, IsCompleted,Due_Date ) values (7,4,2,0,getdate()+2)
insert into #OutputTable (Task_ID, [Type_ID], Item_id, IsCompleted,Due_Date) values (8,4,1,0,getdate()+3 )


SELECT *,ROW_NUMBER() 
        OVER (ORDER BY Due_Date) AS Row_Index  FROM
(
SELECT *, ROW_NUMBER() OVER (PARTITION BY [Type_ID], Item_id ORDER BY Due_Date DESC) as Row  FROM #OutputTable WHERE ISNULL(IsCompleted,0)=0 AND [TYPE_ID] IN (4,5)
) tab where Row = 1 order by task_id


drop table #OutputTable

Open in new window

0
 
LVL 1

Author Closing Comment

by:nkewney
Comment Utility
I think this is perfect.  I had played around with this and got something simliar.

Thank you for helping me clear up my thinking and providing an accurate and concise solution.

Nick
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now