February 14, 2019 at 10:59 am
Create table #tmp(id int,Type char(1))
Insert into #tmp
select 1,'F' union
select 1,'F' union
select 1,'T' union
select 1,'F' union
select 1,'F' union
select 1,'F' union
select 2,'T' union
select 2,'F' union
select 2,'T' union
select 2,'F' union
select 2,'T' union
select 2,'F'union
select 2,'F'
select 3,'T'
I have data that looks somewhat similar like above.The expected output would be to group records for 'F' based on the Type value where Type='T'
So the expected output would be
Id Type Grp
1 'F' 1
1 'F' 1
1 'T' 2
1 'F' 2
1 'F' 2
2 'T' 1
2 'F' 1
2 'T' 2
2 'F' 2
2 'T' 3
2 'F' 3
2 'F' 3
3 'T' 1
Lets take example of Id 1.So start value of Type for Id 1 is 'F' so Grp value assigned would be 1 for that row till it finds a 'T'.Once it finds the next 'T' the grp value assigned for Id 1 would be 2 till it finds the next 'T' for the same Id type .This continues so on and so forth for a unique Id.
Similarly for Id 2.The start value for Id 2 is 'T' so Grp value assigned will be 1 and all the Grp values for Id 2 would be assigned 1 till it finds the next 'T'
February 14, 2019 at 11:45 am
Your logic explanation makes me think that maybe you want either ROW_NUMBER() or COUNT() with a windowing function.SELECT ID
, Type
, ROW_NUMBER() OVER (PARTITION BY ID, Type ORDER BY ID) AS rn
FROM src;
February 14, 2019 at 11:57 am
pietlinden - Thursday, February 14, 2019 11:45 AMYour logic explanation makes me think that maybe you want either ROW_NUMBER() or COUNT() with a windowing function.SELECT ID
, Type
, ROW_NUMBER() OVER (PARTITION BY ID, Type ORDER BY ID) AS rn
FROM src;
Unfortunately this is not what I want.
The grp should be assigned before and after to a "F" based on the presence of "T" for a unique Iid
February 14, 2019 at 12:35 pm
sac.nan - Thursday, February 14, 2019 10:59 AMCreate table #tmp(id int,Type char(1))
Insert into #tmp
select 1,'F' union
select 1,'F' union
select 1,'T' union
select 1,'F' union
select 1,'F' union
select 1,'F' union
select 2,'T' union
select 2,'F' union
select 2,'T' union
select 2,'F' union
select 2,'T' union
select 2,'F'union
select 2,'F'
select 3,'T'I have data that looks somewhat similar like above.The expected output would be to group records for 'F' based on the Type value where Type='T'
So the expected output would be
Id Type Grp
1 'F' 1
1 'F' 1
1 'T' 2
1 'F' 2
1 'F' 2
2 'T' 1
2 'F' 1
2 'T' 2
2 'F' 2
2 'T' 3
2 'F' 3
2 'F' 3
3 'T' 1Lets take example of Id 1.So start value of Type for Id 1 is 'F' so Grp value assigned would be 1 for that row till it finds a 'T'.Once it finds the next 'T' the grp value assigned for Id 1 would be 2 till it finds the next 'T' for the same Id type .This continues so on and so forth for a unique Id.
Similarly for Id 2.The start value for Id 2 is 'T' so Grp value assigned will be 1 and all the Grp values for Id 2 would be assigned 1 till it finds the next 'T'
There is no order to rows on a database table. So what are you proposing ordering them by?
February 14, 2019 at 12:36 pm
You can't given the data provided. SQL tables represent sets, which are unordered. Your output requires an order that is not supported by your data. There is no way to get from your data (as provided) to your expected output.
I have added an identity column to your table to provide a field that will allow you to get the expected output. You will either need to find or add a similar identity or date field in your actual data to get this to work.
Create table #tmp(pk int identity, id int,Type char(1))
Insert into #tmp(id, Type)
select 1,'F' union ALL
select 1,'F' union ALL
select 1,'T' union ALL
select 1,'F' union ALL
select 1,'F' union ALL
select 1,'F' union ALL
select 2,'T' union ALL
select 2,'F' union ALL
select 2,'T' union ALL
select 2,'F' union ALL
select 2,'T' union ALL
select 2,'F'union ALL
select 2,'F' UNION ALL
select 3,'T'
SELECT *, CASE WHEN FIRST_VALUE(Type) OVER(PARTITION BY ID ORDER BY pk ROWS UNBOUNDED PRECEDING) = 'T' THEN 0 ELSE 1 END + COUNT(CASE WHEN Type = 'T' THEN 1 END) OVER(PARTITION BY ID ORDER BY pk ROWS UNBOUNDED PRECEDING)
FROM #tmp
DROP TABLE #tmp
There were also issues with your sample data.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 14, 2019 at 10:40 pm
drew.allen - Thursday, February 14, 2019 12:36 PMYou can't given the data provided. SQL tables represent sets, which are unordered. Your output requires an order that is not supported by your data. There is no way to get from your data (as provided) to your expected output.
I have added an identity column to your table to provide a field that will allow you to get the expected output. You will either need to find or add a similar identity or date field in your actual data to get this to work.
Create table #tmp(pk int identity, id int,Type char(1))
Insert into #tmp(id, Type)
select 1,'F' union ALL
select 1,'F' union ALL
select 1,'T' union ALL
select 1,'F' union ALL
select 1,'F' union ALL
select 1,'F' union ALL
select 2,'T' union ALL
select 2,'F' union ALL
select 2,'T' union ALL
select 2,'F' union ALL
select 2,'T' union ALL
select 2,'F'union ALL
select 2,'F' UNION ALL
select 3,'T'SELECT *, CASE WHEN FIRST_VALUE(Type) OVER(PARTITION BY ID ORDER BY pk ROWS UNBOUNDED PRECEDING) = 'T' THEN 0 ELSE 1 END + COUNT(CASE WHEN Type = 'T' THEN 1 END) OVER(PARTITION BY ID ORDER BY pk ROWS UNBOUNDED PRECEDING)
FROM #tmpDROP TABLE #tmp
There were also issues with your sample data.
- You don't have the same number of records with ID 1 in your data as in your expected results.
- You used a UNION instead of a UNION ALL, so it eliminated all duplicates.
- You forgot to include a UNION ALL for ID 3.
Drew
Apologize for that.I should had tested the sample data before posting it here.
and thank you very very much... after a few tweaks to your query it is working exactly as I wanted it to.
Greatly appreciate your efforts and time.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply