Solved

Grouping records by multiple fields in SQL query

Posted on 2011-02-14
10
213 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
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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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 different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to shrink a transaction log file down to a reasonable size.

805 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