| problem in sql query [message #575076] |
Sat, 19 January 2013 00:37  |
 |
deepikachaudhary31
Messages: 3
Registered: October 2012
|
Junior Member |
|
|
I have a table employee
clientId empid empname active_worked_yn
1 1 a Y
1 2 b N
2 1 c N
I want output
one record from clientId 1 if active_worked_yn = 'Y'
1 1 a Y
2 1 c N
otherwise
1 2 b N
2 1 c N
|
|
|
|
|
|
|
|
| Re: problem in sql query [message #575086 is a reply to message #575085] |
Sat, 19 January 2013 01:33  |
 |
Michel Cadot
Messages: 54223
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Michel Cadot wrote on Sat, 19 January 2013 07:50From your previous topic:
Michel Cadot wrote on Thu, 18 October 2012 08:05Welcome to the forum.
With any SQL question, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.
...
As you refuse to follow the guide and to feedback to people that help you to know if you finally find the solution and which one, I don't any reason to continue to try to help you.
Regards
Michel
[Updated on: Sat, 19 January 2013 01:34] Report message to a moderator |
|
|
|
| Re: problem in sql query [message #575087 is a reply to message #575085] |
Sat, 19 January 2013 01:50 |
flyboy
Messages: 1670
Registered: November 2006
|
Senior Member |
|
|
deepikachaudhary31 wrote on Sat, 19 January 2013 08:30I want this type of output from this table.
I do not see any output in your post. It would be useless without of exact rules leading to it (which are missing too) anyway.
deepikachaudhary31 wrote on Sat, 19 January 2013 08:30I want to check if there multiple records for same clientId
Aggregation by CLIENTID is capable to get this information. Alternatively, analytic COUNT function can tell it too.
deepikachaudhary31 wrote on Sat, 19 January 2013 08:30then check for that clientId worked_active = 'Y'
Place it to appropriate WHERE condition then.
|
|
|
|
| Re: problem in sql query [message #575106 is a reply to message #575076] |
Sat, 19 January 2013 09:03 |
 |
shumail
Messages: 36
Registered: September 2012
Location: Canada
|
Member |
|
|
Check it out.
with data as
(
select empid,clientid, row_number() over (partition by clientid order by clientid desc) cnt,
COUNT(*) OVER (partition by clientid) ctt
from employee)
select data.*,(case when ctt > 1 and cnt=1 then 'Y' else 'N' end) active_worked_yn
from data
;
|
|
|
|
] 
Members
Search
Help
Register
Login
Home
