SQLTeam.com | Weblogs | Forums

Sequential Numbers based on a condition

Good Afternoon. I hope you can help. I am trying to create a SQL query that will add a sequential number to a field, depending on certain criteria. We currently have some products that include components called strips. Some products only have 1 strip others have 2 or more. I would like to get the script to add a number to each row that contains a strip. So if the product has 1 strip the number on that row will be 1. If the product has 2 strips then the first row will be 1 and the second would be 2. I have attached an image that shows what I am trying to achieve.

If this isn't clear let me know and I will try and explain better



Sample data will likely be clearer than text descriptions. Data in an image is not best, better is actual data with CREATE TABLE and INSERT statement(s).

Look up ROW_NUMBER() - that would be my first guess at a solution. You will need to be able to identify the column(s) in the results where the sequence starts (partition) - and what determines first vs second vs third row (order by).

1 Like

Thanks for the assistance, after following your advice I found this website

https ://www. simplilearn. com / tutorials / sql-tutorial / row-number-funtion-in-sql

Which helped me use the row_number() function to achieve my goal.

Thank you for the feedback

I can't overcome the feeling that this was a setup for the spam that was posted as the last entry by the OP. :frowning: Notice that he was even careful about adding spaces to the URL so that an automatic URL detector to prevent spam might not pick it up.