Grouping records by multiple fields in SQL query

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

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 )

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

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

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

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?

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

Select allOpen in new window

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

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