March 3, 2011 at 11:29 pm
declare @invitee table
(
invitee_id int,
guest_id int,
invitee_order int,
guest_order int
)
insert into @invitee
select 0,0 ,0,1 union
select 0,0,2,54 union
select 0,0,1,57
select * from @invitee
i am using below query which is giving me wrong result
SELECT
ROW_NUMBER() OVER( ORDER BY invitee_order) as Invitee_ID,
ROW_NUMBER() OVER( ORDER BY guest_order) as Guest_ID,
invitee_order,
guest_order
FROM @invitee
Here guest_id and invitee_id should be "0" whenever guest_order or invitee_order is 0.
as mentioned in output .
--Required output
invitee_id , guest_id, invitee_order, guest_order
0 1 0 1
1 3 1 57
2 2 2 54
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
March 4, 2011 at 5:39 am
Eddited, because I missed part of requirements. This should perform better than following solution.
;with a as(
Select
ROW_NUMBER() OVER ( Partition by case when invitee_order=0 then 0 else 1 end ORDER BY invitee_order) as Invitee_ID,
ROW_NUMBER() OVER ( Partition by case when guest_order=0 then 0 else 1 end ORDER BY guest_order) as Guest_ID,
invitee_order,
guest_order
FROM @invitee
)
Select
Case when invitee_order=0 then 0 else Invitee_ID end Invitee_ID,
Case when guest_order=0 then 0 else Guest_ID end Guest_ID,
invitee_order,
guest_order
from a
Order By invitee_order
March 4, 2011 at 6:28 am
If I understand the question correctly, this should work:
SELECT Invitee_ID =
CASE
WHEN invitee_order > 0 THEN
ROW_NUMBER() OVER (ORDER BY invitee_order) -
COUNT(CASE WHEN invitee_order = 0 THEN 1 END) OVER ()
ELSE 0
END,
Guest_ID =
CASE
WHEN guest_order > 0 THEN
ROW_NUMBER() OVER (ORDER BY guest_order) -
COUNT(CASE WHEN guest_order = 0 THEN 1 END) OVER ()
ELSE 0
END,
invitee_order,
guest_order
FROM @invitee
ORDER BY
Invitee_ID
;
March 4, 2011 at 6:36 am
SQLkiwi (3/4/2011)
If I understand the question correctly, this should work:
SELECT Invitee_ID =
CASE
WHEN invitee_order > 0 THEN
ROW_NUMBER() OVER (ORDER BY invitee_order) -
COUNT(CASE WHEN invitee_order = 0 THEN 1 END) OVER ()
ELSE 0
END,
Guest_ID =
CASE
WHEN guest_order > 0 THEN
ROW_NUMBER() OVER (ORDER BY guest_order) -
COUNT(CASE WHEN guest_order = 0 THEN 1 END) OVER ()
ELSE 0
END,
invitee_order,
guest_order
FROM @invitee
ORDER BY
Invitee_ID
;
Thanks Paul.
I was looking for this
ROW_NUMBER() OVER (ORDER BY guest_order) -
COUNT(CASE WHEN guest_order = 0 THEN 1 END) OVER ()
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
March 4, 2011 at 7:40 am
No problem. By the way, it could also be expressed this way:
ROW_NUMBER() OVER (ORDER BY guest_order) -
COUNT(NULLIF(ABS(SIGN(guest_order)), 1)) OVER ()
I think the CASE expression is clearer though.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply