September 1, 2009 at 12:58 am
I have a query in which I retrieve GroupId,Number Status.So are 3 types of status - NPL,SPL,PL
When I extract a distinct list of this status based on certain condition
like group by Group ID, I may get the result with all 3 of them.
So in the final select I'm grouping by GroupID and status.
My issue is
if there are all 3 status,I need to display the highest one ie..,
if NPL,SPL,PL - NPL is returned
If SPL,PL-then SPL is returned.
If only PL - then PL returned.
Sample quey is:
select bg.[Group ID],
bg.[Number],
[Status]
from
(select distinct [Number],
[Group ID]
from boga
where [Group ID] is not null) bg,
(select [Number],
[Status]
from Rec
where [indicator] = 'Available') r
where
bg.[Number] = r.[Number]
and bg.[Group ID] in
('212',
'123')
order by bg.[Group ID], bg.[Number], [Status]
Will using CASe stmt work?
September 1, 2009 at 2:26 am
Sample quey is:
select bg.[Group ID],
bg.[Number],
[Status]
from
(select distinct [Number],
[Group ID]
from boga
where [Group ID] is not null) bg,
(select [Number],
[Status]
from Rec
where [indicator] = 'Available') r
where
bg.[Number] = r.[Number]
and bg.[Group ID] in
('212',
'123')
order by bg.[Group ID], bg.[Number], [Status]
Will using CASe stmt work?
I am not sure if I am overlooking something but where is the group by clause?
---------------------------------------------------------------------------------
September 1, 2009 at 4:52 am
Sorry forgot to add the group by clause..
Pls look at the foll query
select bg.[Group ID],
[Status]
from
(select distinct [Number],
[Group ID]
from boga
where [Group ID] is not null) bg,
(select [Number],
[Status]
from Rec
where [indicator] = 'Available') r
where
bg.[Number] = r.[Number]
and bg.[Group ID] in
('233184',
'233236')
group by bg.[Group ID],[Status]
order by bg.[Group ID],[Status]
So here for each Group Id I can have NPL,SPL,or PL..I need to return one status based on the criteria mentioned above
September 1, 2009 at 7:29 am
You should give data like this to get an answer. See if this solution works in your case.
CREATE TABLE Group_Test(Group_ID int, Status varchar(5))
INSERT INTO Group_Test VALUES(1, 'NPL')
INSERT INTO Group_Test VALUES(1, 'SPL')
INSERT INTO Group_Test VALUES(1, 'PL')
INSERT INTO Group_Test VALUES(2, 'SPL')
INSERT INTO Group_Test VALUES(2, 'PL')
INSERT INTO Group_Test VALUES(3, 'PL')
Select * from (Select RANK() OVER (PARTITION BY Group_ID order by Group_ID,
CASE WHEN Status= 'NPL' THEN 1
WHEN Status= 'SPL' THEN 2
WHEN Status= 'PL' THEN 3
ELSE 4 END) as Row_NU, *
FROM Group_Test) test
Where Row_Nu = 1
---------------------------------------------------------------------------------
September 1, 2009 at 11:28 pm
Thank you so much!! The code fits in perfectly:-)
September 1, 2009 at 11:31 pm
Thank you so much!! The code fits in perfectly:-)
September 1, 2009 at 11:43 pm
Thank you so much!! The code fits in perfectly:-)
September 1, 2009 at 11:46 pm
Thank you so much!! The code fits in perfectly:-)
September 2, 2009 at 1:42 am
You are welcome. 🙂
---------------------------------------------------------------------------------
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply