October 20, 2017 at 5:21 am
Hi,
Here is an example of the data:
ID GROUP PEOPLE DATE REQUIRED_FIELD
1 1 1 1 Y
1 2 1 2
1 2 2 3 Y
1 3 1 4
1 3 3 5
1 3 4 6 Y
1 2 1 7
1 2 2 8
1 2 2 9
1 2 2 10 Y
2 3 1 2 Y
2 2 1 3
2 2 2 4 Y
I've used numbers in the example so I don't have to type all the names and dates.
I need to filter out the maximum values for each consecutive group in ID's sorted by date.
Can some please help with this?
the data is on SQL2008R2
Thanks
October 20, 2017 at 5:33 am
Use a CTE to get row numbers partitioned by ID and Group and ordered by date (descending), then select from that CTE where the row number is 1.
John
October 20, 2017 at 5:56 am
John Mitchell-245523 - Friday, October 20, 2017 5:33 AMUse a CTE to get row numbers partitioned by ID and Group and ordered by date (descending), then select from that CTE where the row number is 1.John
Format would be along the lines of:WITH CTE AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY [ID Column] ORDER BY [Date Column] DESC) AS RN
FROM [Your Table])
SELECT *
FROM CTE
WHERE RN = 1;
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 20, 2017 at 6:03 am
sorry I forgot to mention 1 thing
sometimes it happens that the people column has a NULL value and in that case I need to grab the previous non null value available. if it doesn't exists it can stay NULL
sorry again here is the updated example
ID GROUP PEOPLE DATE REQUIRED_FIELD
1 1 1 1 Y
1 2 1 2
1 2 2 3 Y
1 3 1 4
1 3 3 5 Y
1 3 NULL 6
1 1 1 7
1 2 NULL 8 Y
1 2 NULL 9
1 2 NULL 10
2 3 1 2 Y
2 2 1 3
2 2 2 4 Y
I'm not sure how to use CTE so an example would be awesome
Thank you
October 20, 2017 at 7:08 am
Thom A - Friday, October 20, 2017 5:56 AMJohn Mitchell-245523 - Friday, October 20, 2017 5:33 AMUse a CTE to get row numbers partitioned by ID and Group and ordered by date (descending), then select from that CTE where the row number is 1.John
Format would be along the lines of:
WITH CTE AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY [ID Column] ORDER BY [Date Column] DESC) AS RN
FROM [Your Table])
SELECT *
FROM CTE
WHERE RN = 1;
Thanks for all the replies
I've tried this but I can't partition it in the way I need it to be. and I've tried some other options as well
If I do it with Partition by ID and order by date it ignores the groups
If I try it with Partition by ID, Group and order by date it can't get the consecutive groupings right, it groups all of the groups together, but I only need to group by the consecutive groups for each ID
It's kinda like I should partition it by ID, then order it by date and then partition it gain by consecutive Groups after, but I just don't know how to do that
WITH CTE AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY ID,Group ORDER BY Date DESC) AS RN
FROM table)
SELECT *
FROM CTE
ORDER BY ID,Date
October 20, 2017 at 7:23 am
Time for you to provide table DDL in the form of a CREATE TABLE statement, sample data in the form of INSERT statements and expected results based on the sample data, please.
With regard to the NULLs in the PEOPLE column, finding the last non-NULL value isn't a trivial problem. Have a look at this and see whether it helps.
John
October 20, 2017 at 7:38 am
davidvarga086 - Friday, October 20, 2017 7:08 AMThanks for all the repliesI've tried this but I can't partition it in the way I need it to be. and I've tried some other options as well
If I do it with Partition by ID and order by date it ignores the groups
If I try it with Partition by ID, Group and order by date it can't get the consecutive groupings right, it groups all of the groups together, but I only need to group by the consecutive groups for each IDIt's kinda like I should partition it by ID, then order it by date and then partition it gain by consecutive Groups after, but I just don't know how to do that
WITH CTE AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY ID,Group ORDER BY Date DESC) AS RN
FROM table)
SELECT *
FROM CTE
ORDER BY ID,Date
Ahh, I missed that it's consecutive groupings.
Working on a further solution (one I posted a second ago doesn't do quite hat you wanted).
In the mean time, because I've done it, DDL and DLM:USE Sandbox;
GO
CREATE TABLE SampleData
(ID int,
[GROUP] int,
PEOPLE int,
[DATE] int,
REQUIRED_FIELD char(1));
GO
INSERT INTO SampleData
VALUES
(1,1, 1 ,1 ,'Y'),
(1,2, 1 ,2 ,' '),
(1,2, 2 ,3 ,'Y'),
(1,3, 1 ,4 ,' '),
(1,3, 3 ,5 ,'Y'),
(1,3,NULL,6 ,' '),
(1,1, 1 ,7 ,' '),
(1,2,NULL,8 ,'Y'),
(1,2,NULL,9 ,' '),
(1,2,NULL,10,' '),
(2,3, 1 ,2 ,'Y'),
(2,2, 1 ,3 ,' '),
(2,2, 2 ,4 ,'Y');
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 20, 2017 at 7:52 am
OK, think I have it. Note that the "second" group 2 isn't returned, as all PEOPLE in that group have the value NULL:WITH CTE AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY ID ORDER BY [DATE], [GROUP] ) -
ROW_NUMBER() OVER (PARTITION BY ID, [GROUP] ORDER BY [DATE]) AS GroupNum
FROM SampleData),
RN AS(
SELECT *,
ROW_NUMBER() OVER (PARTITION BY ID, GroupNum ORDER BY [DATE] DESC) AS RowNum
FROM CTE
WHERE PEOPLE IS NOT NULL)
SELECT ID, [GROUP], PEOPLE, [DATE], REQUIRED_FIELD
FROM RN
WHERE RowNum = 1;
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 20, 2017 at 8:29 am
Thom A - Friday, October 20, 2017 7:52 AMOK, think I have it. Note that the "second" group 2 isn't returned, as all PEOPLE in that group have the value NULL:WITH CTE AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY ID ORDER BY [DATE], [GROUP] ) -
ROW_NUMBER() OVER (PARTITION BY ID, [GROUP] ORDER BY [DATE]) AS GroupNum
FROM SampleData),
RN AS(
SELECT *,
ROW_NUMBER() OVER (PARTITION BY ID, GroupNum ORDER BY [DATE] DESC) AS RowNum
FROM CTE
WHERE PEOPLE IS NOT NULL)
SELECT ID, [GROUP], PEOPLE, [DATE], REQUIRED_FIELD
FROM RN
WHERE RowNum = 1;
Awesome thank you
after I've added max() on the Date in the select below it was super duper
WITH CTE AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY ID ORDER BY [DATE], [GROUP] ) -
ROW_NUMBER() OVER (PARTITION BY ID, [GROUP] ORDER BY [DATE]) AS GroupNum
FROM SampleData),
RN AS(
SELECT *,
ROW_NUMBER() OVER (PARTITION BY ID, GroupNum ORDER BY [DATE] DESC) AS RowNum
FROM CTE
WHERE PEOPLE IS NOT NULL)
SELECT ID, [GROUP], PEOPLE, MAX([DATE]) Max_Date
FROM RN
WHERE RowNum = 1
GROUP BY ID, [GROUP], PEOPLE,
I still have to go thru the code and what it does exactly so I can use these in the future.
Thanks again
October 20, 2017 at 8:32 am
davidvarga086 - Friday, October 20, 2017 8:29 AMThom A - Friday, October 20, 2017 7:52 AMOK, think I have it. Note that the "second" group 2 isn't returned, as all PEOPLE in that group have the value NULL:WITH CTE AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY ID ORDER BY [DATE], [GROUP] ) -
ROW_NUMBER() OVER (PARTITION BY ID, [GROUP] ORDER BY [DATE]) AS GroupNum
FROM SampleData),
RN AS(
SELECT *,
ROW_NUMBER() OVER (PARTITION BY ID, GroupNum ORDER BY [DATE] DESC) AS RowNum
FROM CTE
WHERE PEOPLE IS NOT NULL)
SELECT ID, [GROUP], PEOPLE, [DATE], REQUIRED_FIELD
FROM RN
WHERE RowNum = 1;Awesome thank you
after I've added max() on the Date in the select below it was super duper
WITH CTE AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY ID ORDER BY [DATE], [GROUP] ) -
ROW_NUMBER() OVER (PARTITION BY ID, [GROUP] ORDER BY [DATE]) AS GroupNum
FROM SampleData),
RN AS(
SELECT *,
ROW_NUMBER() OVER (PARTITION BY ID, GroupNum ORDER BY [DATE] DESC) AS RowNum
FROM CTE
WHERE PEOPLE IS NOT NULL)
SELECT ID, [GROUP], PEOPLE, MAX([DATE]) Max_Date
FROM RN
WHERE RowNum = 1
GROUP BY ID, [GROUP], PEOPLE,I still have to go thru the code and what it does exactly so I can use these in the future.
Thanks again
I thought you wanted to return for each distinct group set. Using that MAX will remove the second set if ID1 Group 1. Is that your intent?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply