If you want to ensure no duplicates enter the table then you should declare your IDENTITY column to be unique either as the primary key or with a unique index...

I wonder sometimes if SQL Server needs a reboot. In a lot of places its Sybase roots are still showing (e.g. CONVERT, DECIMAL/NUMERIC, UPDATE...JOIN, @@, etc.). The need to maintain...

what we want are the supporting objects that will help us run your code on our machine. make it easier for us to help you.

Arrgh, so ignore my last set of results. I was not comparing apples to apples. I just ran all three with the indexes I built and while my solution continues...

Thanks for the test-harness Jacob! I think I managed to remove one use of LAG from my query (and still attain the correct result) and it seems to have dramatically...

Please post DDL (CREATE TABLE) including indexes and constraints and DML (INSERT INTO) statements to allow us to create a test area on our side.

OK, I'll go with it...

This query has a lower plan cost than the other two solutions that seem to satisfy the requirements (Jacob's and Drew's) but I haven't tested...

ssc_san (2/4/2016)

---

Thank you all for the replies!

@jacob Wilkins - You are correct, if there are two or more MtchId's with "Phone", "Online" and other OrdLoc's I want to display only...

I see what you are pointing out now, that before we decide to return an online order we must first know there was a phone order for that OrdID. Sounds...

Yeah, just some misplaced humor I didn't want mistaken for something that might take away from this thread. This written stuff...

This one seems to do quite a bit better than the others on elapsed time:

with cte as (select distinct OrdID

...

Jacob Wilkins (2/3/2016)

---

I think the code below does what you want, if I understand the requirements correctly. It scans each table twice, so if the source tables are very large...

Great, now show...

Thanks for...

Here is an option that I think does a little less work but I am still not crazy about needing to use row_number. I think there is still a better...