Solved

Grouping records by multiple fields in SQL query

Posted on 2011-02-14
10
217 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
10 Comments
 
LVL 11

Expert Comment

by:rajvja
ID: 34887411
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
ID: 34887426
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
ID: 34887451
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
Raise the IQ of Your IT Alerts

From IT major incidents to manufacturing line slowdowns, every business process generates insights that need to reach the people required to take action. You need a platform that integrates with your business tools to create fully enabled DevOps toolchains.

You need xMatters.

 
LVL 10

Expert Comment

by:John Claes
ID: 34887506
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
ID: 34887543
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
 
LVL 1

Author Comment

by:nkewney
ID: 34887546
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
ID: 34887629
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
ID: 34887752
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
ID: 34887826
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
ID: 34887853
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

690 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