Hi there,

I'll just add something.. 🙂

When there's primary and foreign key relationship between two tables, output clause would yield an error.. Sample code below:

USE tempdb

GO

CREATE TABLE table1 (

id...

Hi ankur,

My apologies for my late reply.. I was not able to follow this thread..

It's a pleasure to help.. Regarding your question, I think it would not be possible to...

Hi there,

I'm just trying to help.. Maybe, you can try this out:

;WITH mycte AS (

SELECT partnercode,partnername,skuno,gs1no,ROW_NUMBER() OVER (PARTITION BY partnercode ORDER BY LTRIM(RTRIM(gs1no))) row_num

FROM #gs1 gs1

)

SELECT CASE WHEN row_num=1 THEN...

Thank you very much khawaja.irfan! It really works great.. Nice solution!:-D

Will shortest path algorithm be useful for this problem?

I really need help regarding this one.. Any thoughts?

Thanks..

khawaja.irfan.. Thanks for updating your query.. The idea of using Row_Number is great!

However, there are some instances where it would not produce expected result.. In this case,

id | record_id| date_changed...

@jeff Moden.. The solution of khawaja.irfan is great! There's just special case that it would not display the result I need..

God bless!

Thanks..

Thanks khawaja.irfan.. Your query works 100% for the data the I have provided.. Thanks for providing a great solution!:-)

There is just a special case where the query would not produce...

Just a follow up in regarding this scenario (record_id=2 from the code above):

id | record_id| date_changed | old_state | new_state |

1 | 1 ...

Can the following happen? If so, what do you actually want displayed? Yes id5 would be displayed.. The states that would be included: 0->1->2->4.. Thanks for clarifying..

id | record_id|...

I apologize for that.. I already posted the table creation script together with sample data.. Thanks!