I have dataset grouped by query result in SQL. This data has a ROW_NUMBER value. Here, if the ROW_NUMBER value goes in a sequential value, I want to get the highest ROW_NUMBER value. How can I do this. My SQL query is like this.

  SELECT [_NAME]
      ,[LINE]
      ,[TIMESTAMP]
      ,[Equipment]
      ,[CL_Name]
      ,[Status]
      ,[ROW_NUMBER]
  FROM [dbo].[Deflection]
  where [TIMESTAMP] > DATEADD(day,-1,GETDATE()) and [Status] = 0
  group by [CL_Name], [Equipment], [_NAME], [LINE], [TIMESTAMP], [Status], [ROW_NUMBER]
  order by [ROW_NUMBER] desc, [TIMESTAMP] desc

The output of the query is as follows:

Here, I want the values with the same CL_Name to get the largest ROW_NUMBER value if the ROW_NUMBERs continue in consecutive order, I don't want them to take the others. So I want the output of the correct table to be like this.

I want to get an output like this, but I couldn't. How can I do this?

CodePudding user response：

You can create a new row number rn in descending order for each group, and then just pick #1 for each group. For example:

select x.*,
  row_number() over(partition by cl_name order by [ROW_NUMBER] desc) as rn
from (
  -- your query here
) x
where rn = 1

CodePudding user response：

I would go with:

SELECT CL_Name,
       MAX(ROW_NUMBER)
FROM [dbo].[Deflection]
GROUP BY ROW_NUMBER