How can I find the repeating ROW_NUMBER value in SQL and get the data set with the highest value?

Question

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:

_NAME	LINE	TIMESTAMP	Equipment	CL_Name	ROW_NUMBER
01Pack	2	2023-01-04 16:45:35.673	Pack	A	288
01Pack	2	2023-01-04 16:50:35.673	Pack	A	287
01Pack	2	2023-01-04 16:55:35.673	Pack	A	286
02Pack	3	2023-01-04 23:20:36.063	Pack	B	209
03Pack	4	2023-01-04 23:20:36.063	Pack	C	209
03Pack	4	2023-01-04 23:25:36.067	Pack	C	208
03Pack	4	2023-01-04 23:30:36.073	Pack	C	207
03Pack	4	2023-01-04 23:35:36.077	Pack	C	206
01Pack	2	2023-01-04 23:45:36.067	Pack	A	206
01Pack	2	2023-01-04 23:50:36.073	Pack	A	205
01Pack	2	2023-01-04 23:55:36.077	Pack	A	204

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.

_NAME	LINE	TIMESTAMP	Equipment	CL_Name	ROW_NUMBER
01Pack	2	2023-01-04 16:45:35.673	Pack	A	288
02Pack	3	2023-01-04 23:20:36.063	Pack	B	209
03Pack	4	2023-01-04 23:20:36.063	Pack	C	209
01Pack	2	2023-01-04 23:45:36.067	Pack	A	206

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

As an FYI, `timestamp` is often a poor choice of a column name in SQL Server. `timestamp` is a deprecated synonym for `rowversion`, which is a `binary(8)` value, and has *nothing* to do with date and time values. — Thom A, Jan 05 '23 at 14:22
@ahmed Here ROW_NUMBER always goes in order. For example, since I get a daily data, it starts from ROW_NUMBER = 288 and goes up to ROW_NUMBER =1. Here, my main purpose is to write data every 5 minutes in data with Status = 0. Finding the data with the TIMESTAMP value where the same repeating CL_Name data first started. So getting the highest repeating ROW_NUMBER — burak, Jan 05 '23 at 14:30
@ahmed I updated my question. The Impaler's answer doesn't quite meet what I want. If I do it that way, it gets the data according to a single CL_Name state, and it gets grouped. Actually, that's not what I want. You can see it in my edited question. — burak, Jan 05 '23 at 14:50

score 2 · Answer 1 · answered Jan 05 '23 at 14:22

2

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

answered Jan 05 '23 at 14:22

The Impaler

45,731
9
39
76

Do we really need *another* duplicate of [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group)? – Thom A Jan 05 '23 at 14:28
@Larnu You are right. Please mark it as duplicate. – The Impaler Jan 05 '23 at 14:29
@TheImpaler I tried your answer, but I updated my question again, sorry I wrote it wrong. Based on your answer it's actually grouped by a single CL_Name. But I don't want that. I want to capture the first data with Status = 0 according to the same CL_Name that occurs at different times. Without reducing it to a single CL_Name data. In my updated question, the same data set can occur at different times. I want to evaluate them separately. – burak Jan 05 '23 at 14:56
@Larnu Can I ask you something? – burak Jan 05 '23 at 15:16
I've gone ahead and reopened the question, @burak , as the goal psots have moved, if that's what you were going to ask. – Thom A Jan 05 '23 at 15:48
@Larnu No, that's not it, I'm sorry I'm bothering you. You are a database expert, so I think you can help me, so I want to ask. "https://im.ge/i/qBygVW" . My dataset is like in the photo here. As you can see I have data with the same CL_Name at different times. Here I want to get a single row ROW_NUMBER = 288 for data with different time from grouping, where I get ROW_NUMBER = 279 for data in 2nd red area, and ROW_NUMBER = 277 for data in 3rd red area, in fact, non-repeating data in other red area according to CL_Name. I don't know if I could explain. – burak Jan 05 '23 at 16:26
1

If you have a new question, you should be asking a new question, @burak . – Thom A Jan 05 '23 at 16:27
@Larnu It wasn't a new question, it was actually specific to this question, but thank you anyway – burak Jan 05 '23 at 16:32
Then [edit](https://stackoverflow.com/posts/75019784/edit) your question, @burak , as the comments aren't the right place to ask questions. Though I did review your question after your last edit, I've been in calls most of the afternoon, so I haven't answered it as i haven't had an opportunity. – Thom A Jan 05 '23 at 16:34

score 1 · Accepted Answer · answered Jan 05 '23 at 17:38

You have a gaps and islands problem here, you can solve it using a difference between two row_number functions as the following:

Select Top 1 With Ties 
  _NAME, LINE, TIMESTAMP, Equipment, CL_Name, Status, [ROW_NUMBER]
From
(
  Select *,
    ROW_NUMBER() Over (Order By TIMESTAMP) -
    ROW_NUMBER() Over (Partition By _NAME Order By TIMESTAMP) As grp
  From table_name
) T
Order By ROW_NUMBER() Over (Partition By _NAME, grp Order By [ROW_NUMBER] Desc)

Which is equivalent to the following:

With create_groups AS
(
  Select *,
    ROW_NUMBER() Over (Order By TIMESTAMP) -
    ROW_NUMBER() Over (Partition By _NAME Order By TIMESTAMP) As grp
  From table_name
), 
max_row_num AS
(
  Select *,
    ROW_NUMBER() Over (Partition By _NAME, grp Order By [ROW_NUMBER] Desc) As rn
  From create_groups
)
Select _NAME, LINE, TIMESTAMP, Equipment, CL_Name, Status, [ROW_NUMBER]
From max_row_num
Where rn = 1
Order By _NAME, TIMESTAMP

See demo

Really thank you so much. You've been saving my life for two :) Thank you very much, you did what I was struggling with for hours. — burak, Jan 05 '23 at 17:57

score -1 · Answer 3 · edited Jan 05 '23 at 14:25

-1

I would go with:

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

edited Jan 05 '23 at 14:25

Thom A

88,727
11
45
75

answered Jan 05 '23 at 14:24

DavidC

1
2

This will generate an error. – Thom A Jan 05 '23 at 14:27

How can I find the repeating ROW_NUMBER value in SQL and get the data set with the highest value?

3 Answers3