February 16, 2016 at 12:05 am
Hi,
| metric_name | num |
+----------------+-----+
| F3M_FCST | 1 |
| L3M_SALES | 2 |
| QTR_TRACKING | 3 |
| SALES_VS_PRIOR | 4 |
| F3M_FCST | 1 |
| L3M_SALES | 2 |
| QTR_TRACKING | 3 |
| SALES_VS_PRIOR | 4 |
| F3M_FCST | 1 |
| L3M_SALES | 2 |
| QTR_TRACKING | 3 |
| SALES_VS_PRIOR | 4 |
| F3M_FCST | 1 |
| L3M_SALES | 2 |
| QTR_TRACKING | 3 |
| SALES_VS_PRIOR | 4 |
I need the output like below
| count(*) | metric_name | num |
+----------+----------------+-----+
| 1 | F3M_FCST | 1 |
| 1 | L3M_SALES | 2 |
| 1 | QTR_TRACKING | 3 |
| 1 | SALES_VS_PRIOR | 4 |
| 2 | F3M_FCST | 1 |
| 2 | L3M_SALES | 2 |
| 2 | QTR_TRACKING | 3 |
| 2 | SALES_VS_PRIOR | 4 |
| 3 | F3M_FCST | 1 |
| 3 | L3M_SALES | 2 |
| 3 | QTR_TRACKING | 3 |
| 3 | SALES_VS_PRIOR | 4 |
February 16, 2016 at 1:40 am
Hi and welcome to the forums.
Next time you post a T-SQL question, please provide data in a readily consumable format, such as this:
DECLARE @sampleData TABLE (
metric_name varchar(20),
num tinyint
)
INSERT INTO @sampleData
VALUES
('F3M_FCST',1)
,('L3M_SALES',2)
,('QTR_TRACKING',3)
,('SALES_VS_PRIOR',4)
,('F3M_FCST',1)
,('L3M_SALES',2)
,('QTR_TRACKING',3)
,('SALES_VS_PRIOR',4)
,('F3M_FCST',1)
,('L3M_SALES',2)
,('QTR_TRACKING',3)
,('SALES_VS_PRIOR',4)
,('F3M_FCST',1)
,('L3M_SALES',2)
,('QTR_TRACKING',3)
,('SALES_VS_PRIOR',4)
That said, it is unclear to me what the count(*) column in your desired output represents. It is a count of what?
It looks like your desired output is a repetition of your input data, except the last group of entries. For the remaining rows, "count(*)" looks like the group number, but it is based on nothing else but the physical order of the rows, which is not available in SQL Server unless explicitly enforced by some other column, such as an identity column. Is that what you're after?
-- Gianluca Sartori
February 18, 2016 at 5:32 am
Hi,
I think you may expected below results.
DECLARE @sampleData TABLE (
metric_name varchar(20),
num tinyint
)
INSERT INTO @sampleData
VALUES
('F3M_FCST',1)
,('L3M_SALES',2)
,('QTR_TRACKING',3)
,('SALES_VS_PRIOR',4)
,('F3M_FCST',1)
,('L3M_SALES',2)
,('QTR_TRACKING',3)
,('SALES_VS_PRIOR',4)
,('F3M_FCST',1)
,('L3M_SALES',2)
,('QTR_TRACKING',3)
,('SALES_VS_PRIOR',4)
,('F3M_FCST',1)
,('L3M_SALES',2)
,('QTR_TRACKING',3)
,('SALES_VS_PRIOR',4)
select * from ( select *,ROW_NUMBER()over(PARTITION by num order by num asc)as count from @sampleData ) a order by count,num
--chalam
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply