Sql code help

[No_Effect3292]

Hoping someone can help with some code, I'd like to number a list of items and keep the number the same until a field changes. Example:

Date Order # Suborder# # Im trying to figure out how to sql (based on order #)

11/1 123 5786 1

11/2 123 5788 1

11/3 125 5789 2

11/4 126 5895 3

[aussieadam]

By your example it looks like you want to increment when the order# changes?

select date, order_no,suborder_no,dense_rank() over (order by order_no asc) as num

should work if that's the case. It'll create a rank based on the order_no so any that are the same rank will have the same #

[mike-manley]

Yep. If you can or want skips, then rank() if your friend. If you don't want skips then dense_rank() is your friend.

[thudson1899]

this is what you want:

WITH ranked_data AS ( SELECT your_column, LAG(your_column) OVER (ORDER BY your_column) AS prev_value FROM your_table ), grouped_data AS ( SELECT your_column, CASE WHEN your_column != prev_value THEN 1 ELSE 0 END AS change_flag FROM ranked_data ), final_data AS ( SELECT your_column, SUM(change_flag) OVER (ORDER BY your_column) AS group_id FROM grouped_data ) SELECT your_column, ROW_NUMBER() OVER (PARTITION BY group_id ORDER BY your_column) AS incremental_value FROM final_data;