How to write this SQL

rchiu5hk · #1 (**permalink**) 11-25-10, 20:13

Dear All,

I have the table datas as below:
OrderHeaderID OrderDetailID
5001 6001
5001 6002
5001 6003
5002 6004
5002 6005

How to write SQL for showing as below??
5001 0
5001 1
5001 2
5002 0
5002 1

r937 · #2 (**permalink**) 11-25-10, 20:26

Code:

SELECT t.OrderHeaderID 
     , ( SELECT COUNT(*)
           FROM datas
          WHERE OrderHeaderID = t.OrderHeaderID
            AND OrderDetailID < t.OrderDetailID ) AS cnt
  FROM datas AS t

Internet Services

Web Development

Digital Marketing

Consumer Tech