Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 223
  • Last Modified:

Grouping records by multiple fields in SQL query

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
nkewney
Asked:
nkewney
  • 5
  • 4
1 Solution
 
rajvjaCommented:
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
 
John ClaesCommented:
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
 
nkewneyAuthor Commented:
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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
John ClaesCommented:
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
 
John ClaesCommented:
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
 
nkewneyAuthor Commented:
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
 
John ClaesCommented:
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
 
nkewneyAuthor Commented:
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
 
John ClaesCommented:
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
 
nkewneyAuthor Commented:
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

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now