Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 need query

Author  Topic 

senthil_mca80
Starting Member

10 Posts

Posted - 2008-02-08 : 18:03:31
Hi,

i need the query for the following

Table: Resultset

TestId status
901 Pass
901 Pass
902 pass
902 Fail
902 Pass
903 Pass
903 Blocked
904 Pass
...

Now i want the result set like the follow

TestId status
901 PAss
902 Fail
903 Blocked
904 Pass

My scenario is the test id should return pass only all the instance of the test id get passed.

Otherwise it should show other status like “Failed” or “ Blocked” …

Can anyone help this?

Your help is much appreciated.

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-02-08 : 18:23:54
What if a TestId has a status of Blocked and Fail? Which would win in that scenario?
Go to Top of Page

senthil_mca80
Starting Member

10 Posts

Posted - 2008-02-08 : 18:27:01
IT would be either Blocked or failed
Go to Top of Page

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-02-08 : 18:28:58
[code]SELECT TestId,
CASE MAX(Status)
WHEN 0 THEN 'Pass'
WHEN 1 THEN 'Fail'
WHEN 2 THEN 'Blocked'
END AS Status
FROM ( SELECT TestId,
CASE Status
WHEN 'Pass' THEN 0
WHEN 'Fail' THEN 1
WHEN 'Blocked' THEN 2
END AS Status
FROM Resultset
) a
GROUP BY TestId[/code]
Go to Top of Page

senthil_mca80
Starting Member

10 Posts

Posted - 2008-02-08 : 18:40:49
Hey

you rock....

I got my answere.. actually this was a samll portion of my large query. i was struggling to group this data. but finally u gave me a good solution...

Thanks a lot man...

Senthil
Go to Top of Page
   

- Advertisement -