Compare two values from the same column

[former_member838731]

I would like to get an indication of 1 or 0 every time a number is repeated in the same column (after I have sorted the column)

How can I get such an indication?

[AlexGourdet]

Thank you for visiting SAP Community to get answers to your questions.

Since you're asking a question here for the first time, I'd like to recommend you with the following steps so you can get the most out of your community membership:

• Please see: https://community.sap.com/resources/questions-and-answers as this resource page provides tips for preparing questions that draw responses from our members
• Consider to take our Q&A tutorial at: https://developers.sap.com/tutorials/community-qa.html. as that will help you when submitting questions to the community.

I also recommend that you include a profile picture. By personalizing your profile, you encourage readers to respond: https://developers.sap.com/tutorials/community-profile.html.

I hope you find this advice useful, and we're happy to have you as part of SAP Community!

All the best,
-Alex

[JesperB1]

Hello מיכל דוד,

Is this column the result of a query?

Thanks,

Jesper

[former_member838731]

Hey Jesper,

Yes it is.

[Johan_H · Accepted solution]

Hi מיכל דוד,

If you are running MS SQL Server 2012 or higher, you can use the LAG function. For example:

select ItemCode AS [The ItemCode on this row]
 ,lag(ItemCode, 1, 'There is no previous row') over (order by ItemCode) AS [The ItemCode on the previous row]
 from OITM<br>

or

SELECT x.[curr] AS [ItemCode]
 ,CASE
   WHEN x.[curr] = x.[prev] THEN 1
   ELSE 0
  END AS [1 = same, 0 = different]
 FROM (select ItemCode as [curr]
      ,lag(ItemCode, 1, 'There is no previous row') over (order by ItemCode) as [prev]
 from QUT1) x

Regards,

Johan

[jitin_chawla]

Hi,

If you want to do it in Excel you can use the formula as in example below:

Make a new column as Number of Occurrence and then in the first row of the value field, amend the Formula as per your Column and row number. This will list the number of occurrence of each number in the column list.

Kr,

Jitin

[former_member838731]

Hello Jitin,

Thanks for your answer, but I am trying to do so at the Sap system, without downloading it to Excel.

Is it possible?

[jitin_chawla]

Hi,

Please update the query so that we can check here.

Kr,

Jitin

[JesperB1]

Hello,

You can use Case with Count - on this link is an example https://stackoverflow.com/questions/17975229/using-sql-count-in-a-case-statement

I hope that helps,

Jesper

[gonzalogomez]

SELECT T0.[Docnum], ROW_NUMBER() OVER(PARTITION BY T0.DocNum ORDER BY T0.DocNum Asc) as NVeces 
FROM ORDR T0 order by t0.DocNum