-1

Here I have sample data like

Plan1 - 1
Plan1 - 3
Plan1 - 2
Plan1 - 5
Plan2 - 4
Plan2 - 1

I need to show like this

Plan1 - 1 - 0
Plan1 - 2 - 1
Plan1 - 3 - 2
Plan1 - 5 - 3
Plan2 - 1 - 0
Plan2 - 4 - 2

Can you please provide the solution in c# or sql query?

closed as unclear what you're asking by Scott Hodgin, ypercubeᵀᴹ, RDFozz, Max Vernon, LowlyDBA Sep 14 '18 at 20:42

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    Which DBMS are you using? "SQL" is just a query language, not the name of a specific database product. Please add the tag for the database product you are using postgresql, oracle, db2, sql-server, ... – a_horse_with_no_name Sep 14 '18 at 19:45
  • 1
    I've fixed your formatting a little, but your question is a bit unclear: what SQLplatform is this? What version? Your two lists don't really match, are they supposed to be the same list in a different order? – BradC Sep 14 '18 at 19:46
  • Or, perhaps, the same list with an unspecified formatting rule applied (in which case, knowing the rule would be critical). – RDFozz Sep 14 '18 at 19:49
1

It looks like a ROW_NUMBER() OVER (PARTITION BY …), although

Plan2 - 4 - 2

throws it off... Assuming it should be

Plan2 - 4 - 1

and assuming a simple table structure:

create table temp
(planName VARCHAR(5),
 ID TINYINT);

 insert into temp
VALUES
('Plan1',1),
('Plan1',3),
('Plan1',2),
('Plan1',5),
('Plan2',4),
('Plan2',1)

In SQL Server something like this (fiddle):

SELECT *, (ROW_NUMBER() OVER (PARTITION BY planName ORDER BY ID))-1 AS RowNumber FROM #temp

In MySQL it's less fun (fiddle):

select 
  planName,
  ID,
  RowNumber-1 as RowNumber
from 
(
  select 
    planName,
    ID,
    @row_number:=CASE
      WHEN @planName = planName THEN @row_number +1
      ELSE 1
    END AS RowNumber,
    @planName:=planName as setVar
  from temp
  cross join (SELECT @planName:=min(planName),@row_number:=0 from temp) as t
    order by planName, id
) t

Not the answer you're looking for? Browse other questions tagged or ask your own question.