asked
how to select range values
Dear Gurus Good day to you I am stuck in a problem today the only name of SSC came in my mind so i am writing it to you guys as i know you are solution gurus the problem is this i have a table as script is given below create table Foo(CId int,Coupon varchar(7)) insert Foo Select 1,'AA9997' Union ALL Select 1,'AA9998' Union ALL Select 1,'AA9999' Union ALL Select 1,'AB0001' Union ALL Select 2,'AB0002' Union ALL Select 2,'AB0003' Union ALL Select 3,'AB0004' Union ALL Select 1,'AB0007' Union ALL Select 1,'AB0008' and i want result as given below : --ID FROM Till --1 AA9997 AB0001 --1 AB0007 AB0008 --2 AB0002 AB0003 --3 AB0004 AB0004 or Blank please assist me to get out of this problem.
given that you have 2 rows for CId=1 in the output, how are you identifying these as separate groups? If there is some kind of order implied, you must specify it otherwise the results will be unordered.
Looks like the ORDER BY is on the Coupon field, and the Id is a secondary grouping...
Yeah, if you do it alphabetically. The problem is that there's a loop in the numeric part of the coupon field, and a jump in the alpha part... which is why I was vaguely looking for a sequence grouping type solution. And failing to find one. Put it down to lack of sleep and too many meetings.
OK, here's a clunky solution that's broken down for your pleasure... There may be a couple of steps that can be rammed together, but, as I say, this is a learning exercise. Notes: * `@Foo` - your source data. I've moved it into a table variable rather than a temporary table, because I'm lazy. * `SELECT * FROM @Foo` - just checking the data has loaded OK. * `WITH` - Using various CTEs (Common Table Expressions) to make the steps more intuitive. * `Foo2` - I did have a ROW_NUMBER() function here, but it wasn't required, so I've removed it. Just explaining why the CTE numbering is off. :-) * `Foo3` - using the [`LAG`][1] function to identify the CId field from the previous row in the results as ordered by Coupon * `Foo4` - using the value from the previous step to put a marker on each row where the CId changes * `Foo5` - using [running totals][2] of those markers to get the number of these CId changes so far - this will be used to group the output * Final SELECT Any questions? DECLARE @Foo TABLE ( CId INT , Coupon VARCHAR(7) ); INSERT @Foo SELECT 1 , 'AA9997' UNION ALL SELECT 1 , 'AA9998' UNION ALL SELECT 1 , 'AA9999' UNION ALL SELECT 1 , 'AB0001' UNION ALL SELECT 2 , 'AB0002' UNION ALL SELECT 2 , 'AB0003' UNION ALL SELECT 3 , 'AB0004' UNION ALL SELECT 1 , 'AB0007' UNION ALL SELECT 1 , 'AB0008'; SELECT * FROM @Foo; WITH Foo3 AS ( SELECT * , LAG(CId) OVER ( ORDER BY Coupon ) AS PrevCId FROM @Foo ), Foo4 AS ( SELECT * , CASE WHEN PrevCId <> CId OR Foo3.PrevCId IS NULL THEN 1 ELSE 0 END AS isStart FROM Foo3 ), Foo5 AS ( SELECT * , SUM(Foo4.isStart) OVER ( ORDER BY Coupon ROWS BETWEEN UNBOUNDED PRECEDING AND 0 PRECEDING ) AS RangeMarker FROM Foo4 ) SELECT CId , MIN(Coupon) AS [From] , MAX(Coupon) AS [Till] FROM Foo5 GROUP BY CId , RangeMarker ORDER BY Foo5.CId , [From]; [1]:
https://msdn.microsoft.com/en-us/library/hh231256.aspx [2]:
http://dba.stackexchange.com/a/42988/1295
18 People are following this question.
