sql - Most Efficient Way To Filter Revisioned Order Numbers -

i have issue filtering out orders in t-sql database, use format "q123456" assign order number, , appended "-x" (x being revision number). need eliminate of older revisions (and original without "-x") if newer 1 present.

i know can use wildcard characters this, , check strings against each other, of answers seem extremely inefficient running ridiculous number of comparisons. run time gets out of control, , imagine there better structure utilize.

thank in advance, if need anymore information let me know , i'll best provide it.

edit: example data, have orders: q123456 q123456-1 q123456-2 q134567

i need remove q123456 , q123456-1 results of queue, because of presence of q123456-2. q124567 result date entry order.

if can assume:

• all orders 7 characters in length except when have revision....
• no revision exceeds '-9' , if orders displayed -xx leading 0 (-01...-10): if not have more work. take right part of order - , cast integer , order , assign 0 when no - exists.
• order_num "order_number in table.

we can use window function , common table expression generate row number each order_number series (q123456 of q123456,q123456-1) , select 1 highest order_number (ordered order num desc)

with cte (select a.*, row_number() on (partition  substring(order_num,1,7)                                              order order_num desc) rn              table) select * cte rn = 1 

without cte...

select *  (select a.*, row_number() on (partition  substring(order_num,1,7)                                       order order_num desc) rn       table) rn = 1