How to Return a Incremental Group Number Per Group in SQL

How to increment an integer based on a group in SQL

You can use row_number():

select . . .,
       row_number() over (partition by jl.CustomObjectName order by (select NULL)) as columnNumber

Increment Row Number on Group

That can be accomplished with the DENSE_RANK() function:

  DENSE_RANK() OVER(Order By [shade]) as t_index

Incrementing Count within a Group By

You need to simply use PARTITION BY:

SELECT
   user_id,
   id AS message_id,
   sent_at,
   ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY user_id, sent_at) AS counter
FROM messages
ORDER BY
    user_id,
    sent_at;

Incremental group number per specific hour in datetime attribute in SQL Server

You can use dense_rank(). Here is one method:

select t.*,
       dense_rank() over (order by cast(datetime as date), datepart(hour, datetime)) - 1 as hournumber
from timeTest  t;

Note: This will ignore missing hours. If you want the number of hours since the earliest hour, use datediff() instead:

select t.*,
       datediff(hour, min(datetime) over (), datetime) as hournumber
from timeTest  t;

increment numbers on each group of IDs

select  if(@i = id, @n:=@n+1, @n:=1) auto, @i:=id id, year 
    from thetable cross join (select @i:="") i cross join (select @n:=1) n 
  order by id, year

Auto-increment with Group BY

GROUP BY and variables don't necessarily work as expected. Just use a subquery:

SELECT (@i := @i + 1) AS Sno, c.*
FROM (SELECT c.ContractNo, c.SoftwareName, c.CompanyName, cu.InvoiceNo, cu.InvoiceDate,
             cu.InvAmount, cu.InvoicePF, max(cu.InvoicePT) AS InvoicePeriodTo, cu.InvoiceRD, cu.ISD
      FROM contract c JOIN
           contractuser as b
           ON c.ContractNo = cu.ContractNo
      GROUP BY cu.ContractNo
      ORDER BY c.SoftwareName ASC
     ) c CROSS JOIN
     (SELECT @i := 0) params;

Notes:

I also fixed the JOIN syntax. Never use commas in the FROM clause.
I also added reasonable table aliases -- abbreviations for the tables. a and b don't mean anything, so they make the query harder to follow.
I left the GROUP BY with only one key. It should really have all the unaggregated keys but this is allowed under some circumstances.

SQL - Window Function to Increment Group Number

This is a variation of the groups-and-islands problem. You can use the difference of row numbers to describe the group:

select t.*,
       dense_rank() over (partition by patient_id order by first_contact) as location_name
from (select t.*,
             min(contact_date) over (partition by patient_id, location_name, seqnum - seqnum_2) as desired_result
      from (select t.*,
                   row_number() over (partition by patient_id order by contact_date) as seqnum,
                   row_number() over (partition by patient_id, location_name order by contact_date) as seqnum_2
            from t
           ) t
      ) t;