November 11, 2014 at 8:07 am
I'm partitioning data to group by lead_id and order by date in descending order with the following
query:
select top 1000 lead_id, activity_type_code, activity_date,
row_number() over(partition by lead_id order by activity_date desc) rownum
from [dbo].[PartitionExample]
how do I modify this script to return only the partitions that have an 'M'? I need the whole partition, not just the row of the partition having an 'M'. For example, if I use filter WHERE activity_type_code = 'M' then only the row of each partition having the 'M' is returned
But, I need the whole partition, so in the table below, I would want all 5 rows of the lead_id = 159 partition.
DDL (generated by SSL task. the third column will render as datetime when insert is run)
CREATE TABLE [dbo].[PartitionExample](
[lead_id] [int] NOT NULL,
[activity_type_code] [varchar](10) NOT NULL,
[activity_date] [smalldatetime] NOT NULL,
[rownum] [bigint] NULL
)
----the 3rd row is date column, generate script task turns it into hexadecimal, but when run produces regular datetime column
INSERT [dbo].[PartitionExample] ([lead_id], [activity_type_code], [activity_date], [rownum]) VALUES (147, N'PI', CAST(0x9108031E AS SmallDateTime), 1)
INSERT [dbo].[PartitionExample] ([lead_id], [activity_type_code], [activity_date], [rownum]) VALUES (147, N'PI', CAST(0x9108031E AS SmallDateTime), 2)
INSERT [dbo].[PartitionExample] ([lead_id], [activity_type_code], [activity_date], [rownum]) VALUES (159, N'M', CAST(0x91310448 AS SmallDateTime), 1)
INSERT [dbo].[PartitionExample] ([lead_id], [activity_type_code], [activity_date], [rownum]) VALUES (159, N'RA', CAST(0x91310447 AS SmallDateTime), 2)
INSERT [dbo].[PartitionExample] ([lead_id], [activity_type_code], [activity_date], [rownum]) VALUES (159, N'RA', CAST(0x91310445 AS SmallDateTime), 3)
INSERT [dbo].[PartitionExample] ([lead_id], [activity_type_code], [activity_date], [rownum]) VALUES (159, N'RA', CAST(0x91310443 AS SmallDateTime), 4)
INSERT [dbo].[PartitionExample] ([lead_id], [activity_type_code], [activity_date], [rownum]) VALUES (159, N'RA', CAST(0x91310442 AS SmallDateTime), 5)
INSERT [dbo].[PartitionExample] ([lead_id], [activity_type_code], [activity_date], [rownum]) VALUES (161, N'PI', CAST(0x910B0223 AS SmallDateTime), 1)
INSERT [dbo].[PartitionExample] ([lead_id], [activity_type_code], [activity_date], [rownum]) VALUES (161, N'RA', CAST(0x910B0223 AS SmallDateTime), 2)
I searched for 'returned all rows of a partition sql query' and came found nothing. Kindly, do you have suggestions? Thanks.
November 11, 2014 at 9:22 am
Correlated subqueries are your friend...
select top 1000 lead_id, activity_type_code, activity_date,
row_number() over(partition by lead_id order by activity_date desc) rownum
from [PartitionExample] pe
WHERE EXISTS (SELECT 1 FROM PartitionExample WHERE activity_type_code = 'M' AND lead_id=pe.lead_id);
November 11, 2014 at 9:23 am
Right, First question to consider is what is unique about all set that has the row with a value of 'M'
This should work
SELECT
*
FROM PartitionExample OuterPartition
JOIN
(SELECT Lead_ID
FROM PartitionExample InnerPartition
WHERE Activity_Type_Code='M') InnerPartition
ON InnerPartition.Lead_ID=OuterPartition.Lead_ID
That should do the job and return the rows you require, it becomes more difficult if you have multiple sets with the 'M' activity code.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
November 11, 2014 at 9:24 am
pietlinden (11/11/2014)
Correlated subqueries are your friend...
select top 1000 lead_id, activity_type_code, activity_date,row_number() over(partition by lead_id order by activity_date desc) rownum
from [PartitionExample] pe
WHERE EXISTS (SELECT 1 FROM PartitionExample WHERE activity_type_code = 'M' AND lead_id=pe.lead_id);
wont that only return one row, as that is all the EXISTS set returns?
_________________________________________________________________________
SSC Guide to Posting and Best Practices
November 11, 2014 at 9:28 am
returns all the leads where lead_id = 159, because at least one in the set contains an 'M' activity type code. If you run it, it returns 5 records.
November 11, 2014 at 9:46 am
I thought with the additional filter on the correlated sub query it would act as an exclusive filter, for that one row, but I'm happy to be wrong.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
November 11, 2014 at 2:58 pm
I think you're right... you don't need an EXISTS subquery... you can do all this with a join. =)
November 11, 2014 at 3:19 pm
Jason and peitlinden. both marked as answer. Thanks much for help.
Regarding 'correlated queries are your friend'....seems to be true lately, peitlinden. Thanks for sticking by.
November 11, 2014 at 3:35 pm
peitlinden's ends up working better for me because it allows me to order the dates within the partition, but that wasn't part of the ask....:-)
November 11, 2014 at 3:43 pm
thanks for the easily consumable data... (the only thing missing was the "extra" data so that you're sure it gets excluded).
Found this article discussing IN vs EXISTS, which was interesting... I'm pretty sure GilaMonster has an article on it.
http://explainextended.com/2009/06/16/in-vs-join-vs-exists/
Yep, found Gail's article... well worth a read.
http://sqlinthewild.co.za/index.php/2009/08/17/exists-vs-in/
Her explanation is great, and really easy to understand.
November 12, 2014 at 4:22 am
@pietlinden, It just shows there are multiple ways of accomplishing the same task, and to be pragmatic, as in some circumstances one solution may perform slightly better than the other. =)
pietlinden (11/11/2014)
thanks for the easily consumable data... (the only thing missing was the "extra" data so that you're sure it gets excluded).Found this article discussing IN vs EXISTS, which was interesting... I'm pretty sure GilaMonster has an article on it.
http://explainextended.com/2009/06/16/in-vs-join-vs-exists/
Yep, found Gail's article... well worth a read.
http://sqlinthewild.co.za/index.php/2009/08/17/exists-vs-in/
Her explanation is great, and really easy to understand.
That article by Gail is very good, as are a number of others, also its worth taking a look at one on catch-all queries, especially if you are heavily into reporting.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
November 12, 2014 at 7:41 am
top article: JOIN, EXISTS, IN capable of producing the same stats.
Gail. Exists doesn’t check for a match, it doesn’t care in the slightest what values are been returned from the expression, it just checks for whether a row exists or not...to use EXISTS to do the same kind of thing as IN, there must be a correlation predicate within the subquery.
Pietlinden I think you used correlation predicate. I better return to my 'takeaway' query and double check all good. Thanks for reading material.
November 12, 2014 at 12:51 pm
...
November 12, 2014 at 1:43 pm
...
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply