T-SQL Query Help

select id,ordername,ordernum,trailernum,city,@orderdetailid from
orders
where id in
(1,2,3,4)


Each ID will return multiple records in this query.

My requirement is to get an incremental value starting 1 for @orderdetailid.

eg: if id=1 returns 4 records and id=2 returns 2 records the output should be like the below


id ............ @orderdetailid
1                     1
1                     2
1                     3
1                     4
2                     1
2                     2

Can you please help me with this. I want to avoid using a cursor or loop here.

Thanks,
LVL 4
rocky_lotus_newbieAsked:
Who is Participating?
 
Paul MacDonaldConnect With a Mentor Director, Information SystemsCommented:
Can you just:

select id,ordername,ordernum,trailernum,city,@orderdetailid from
orders
where id in
(1,2,3,4)
ORDER BY ID, @orderdetailid
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
Why is this a variable: @orderdetailid

looks like there should be a join to the orders_details table.
0
 
mayank_joshiConnect With a Mentor Commented:
you may try this:-
WITH cte AS
(
select id,ordername,ordernum,trailernum,city,ROW_NUMBER() OVER ( PARTITION BY id ORDER BY ordernum ) AS 'orderdetailid' from  orders where id in (1,2,3,4)
) 
SELECT * FROM cte order by id,orderdetailid

Open in new window

0
 
mayank_joshiCommented:
in the portion PARTITION BY id ORDER BY ordernum you can use any column(s) after order by
 as per your requirement
e.g.,

WITH cte AS
(
select id,ordername,ordernum,trailernum,city,ROW_NUMBER() OVER ( PARTITION BY id ORDER BY ordernum,ordername ) AS 'orderdetailid' from  orders where id in (1,2,3,4)
) 
SELECT * FROM cte order by id,orderdetailid

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.