J Livingston SQL (4/29/2016)
Jacob Wilkins (4/29/2016)
J Livingston SQL (4/29/2016)
Jacob...dont seem to get the results expected from your code in comparison to mine.....have I misread your code:You didn't misread it, but the sample data doesn't conform to a rule that the OP's data did (and I assumed in my solution), which is that a given bus can be of just one type. Just partition the ROW_NUMBER by bus type in addition to bus name and they should match.
I'll have to confirm later.
Cheers!
got it !...nice solution
Thanks! Yours works pretty nicely as well. It tends to perform better than my initial one in terms of CPU and duration.
I have a feeling they would be much closer if instead of doing the lazy DISTINCT subquery I either 1) did a ROW_NUMBER partitioned by all columns in the subquery and added a WHERE row_num=1 to get rid of duplicates, or 2) skipped the subquery altogether and just replaced the ROW_NUMBER with DENSE_RANK, since then duplicates wouldn't matter.
I'll have to test that out in a bit.
Cheers!