How to Get the Max Row Number Per Group/Partition in SQL Server

How to get the max row number per group/partition in SQL Server?

Try this:

SELECT a.*, CASE WHEN totalPayments>1 THEN 'NO' ELSE 'YES' END IsFirstPayment
  FROM(
                SELECT  p.payment_id,     
                                p.user_id,     
                                ROW_NUMBER() OVER (PARTITION BY p.user_id ORDER BY p.payment_date DESC) AS paymentNumber,
                                SUM(1) OVER (PARTITION BY p.user_id) AS totalPayments
                    FROM payment p 
            ) a
WHERE   paymentNumber = 1       

How to get the max row number in the Partition over by SQL Syntax?

This is not addressing your question but I think this is what you want. This will give you the last ProcessDate with one SELECT:

SELECT 
    [FileName]
    ,MAX (ProcessDate) AS LastProcessDate
    ,COUNT (*) FileNameOccurence
FROM StagingTable
GROUP BY [FileName]

If so, don't make it more complicated with nested queries.

Row_number over partition and find the max rn value

Use max window function.

SELECT T.*,MAX(rn) OVER(PARTITION BY OrderNo) AS rn_max
FROM (
select OrderNO,PartCode,Quantity,row_number() over(partition by OrderNO order by DateEntered desc) as rn
from YourTable
) T

Edit: An easier option is to use count as suggested by @Jason A. Long in the comments.

select OrderNO
       ,PartCode
       ,Quantity
       ,row_number() over(partition by OrderNO order by DateEntered desc) as rn
       ,count(*) over(partition by OrderNO) as maxrn
from YourTable

How to get only the max row number row of record?

Given your logic, there does not seem to be a maximum row number. Or is the row numbering tied to another field than ProcessDate?

As far as I understand your problem, I would just use the following query:

SELECT [FileName], MAX([ProcessDate]) AS [ProcessDate], COUNT(*) AS [Count]
FROM [StagingTable]
GROUP BY [FileName]

SQL select the row with max value using row_number() or rank()

If you use rank() you can get multiple results when a name has more than 1 row with the same max value. If that is what you are wanting, then switch row_number() to rank() in the following examples.

For the highest value per name (top 1 per group), using row_number()

select sub.RowId, sub.Name, sub.Value
from (
  select *
    , rn = row_number() over (
        partition by Name 
        order by Value desc
      )
  from t
  ) as sub
where sub.rn = 1

---

I can not say that there are any 'better' alternatives, but there are alternatives. Performance may vary.

cross apply version:

select distinct
    x.RowId
  , t.Name
  , x.Value
from t
  cross apply (
    select top 1
          *
      from t as i
      where i.Name = t.Name
      order by i.Value desc
    ) as x;

---

top with ties using row_number() version:

select top 1 with ties
    *
  from t
  order by 
    row_number() over (
      partition by Name
      order by Value desc
      )

---

This inner join version has the same issue as using rank() instead of row_number() in that you can get multiple results for the same name if a name has more than one row with the same max value.

inner join version:

select t.*
from t
  inner join (
    select MaxValue = max(value), Name
    from t
    group by Name
    ) as m
      on t.Name  = m.Name
     and t.Value = m.MaxValue;

How to get MAX rownumber with rowNumber in same query

I think you want count(*) as a window function:

SELECT ROW_NUMBER() OVER (ORDER BY NULL) as rnum,
       COUNT(*) OVER () as cnt,
       C.ID, C.FIELD1, C."NAME", C.FIELD2, C.FIELD3
FROM SCHEMA.TABLE 
WHERE C.IS_CRNT = 1

how to select max of row number data in a table in sql?

USE Row_NUMBER() and Partition BY:

;With T AS
(
    SELECT
        ID,
        Client,
        Some_Value,
        Row_No,
        Row_NUMBER() OVER(Partition BY ID Order BY Row_No Desc) AS PartNo
    FROM TableName
)
SELECT
    ID,
    Client,
    Some_Value,
    Row_No
FROM T
WHERE T.PartNo=1

Update Statement Example:

;With T AS
(
    SELECT
        ID,
        Client,
        Some_Value,
        Row_No,
        Row_NUMBER() OVER(Partition BY ID Order BY Row_No Desc) AS PartNo
    FROM TableName
)
Update TableName
SET Name=T.Name
FROM T
WHERE T.PartNo=1 
AND TableName.Id=T.Id

How to get the maximum row_number() for each group in hive sql

select id,max(seq) over(partition by id ORDER BY id)from 
(select *, row_number() over(partition by id ORDER BY id) as seq
from 
table)maxseq
group by id

---

Related Topics