SQLTeam.com | Weblogs | Forums

Best way to query

tsql

#1

I have a table called orders and it has a customerid

I want to be able to run a query and pull up all orders that have a customerid one after the other (so we can check for duplicates)

what would be the best way to query this?


#2

what do you mean by that exactly ?

Please post some sample data and expected result


#3

sample date would be

orderid customerid
1 2
2 3
3 4
4 4
5 5
6 6
7 7
8 4
9 8
10 100

I would want to see orderid 3 & 4 come up as it's 2 consecutive orderids with the same customerid


#4
SELECT o.orderid, o.customerid, o.orderid + 1 AS dup_order
FROM orders o
WHERE EXISTS(
    SELECT 1
    FROM orders o2
    WHERE o2.orderid = o.orderid + 1
    AND o2.customerid = o.customerid
    )