An interview question...
A couple of days ago I read an article "One of the toughest job-interview questions ever". I was reminded of it by this posting...
I found the original post interesting - mostly because I liked the answer the technical writer gave. A bit of background - someone interviewing for a technical writing position is asked what is clearly a "hard core, heads down, write code programmer question". The question seemed entirely inappropriate for the position - but - the answer given was great (I thought)
The answer consisted of lots of questions - in effect - a lot of push back. Define this, specify that, clarify this - need more information.
I can relate.
What surprised me was that a lot of the feedback was negative. A lot of people said "would never hire you", "you missed the point".
All of the time I was reading though, I was nodding my head saying "yeah, what about that". I would have hired him on the spot. Critical thinking, push back, give me the details, tell me what you are really trying to do.
The programmer that rolls over and just answers the question - without enough information to actually answer the question - should send the interviewer running away. But that is apparently what a lot of interviewers are looking for.
I've been known to have three to four very simple interview questions for "Oracle people". They are designed to test the simple to the sublime. They are:
I have a table:
create table t ( ....., month number, ..... );
Month is always a number between 1 and 12.
I ask three questions about this table:
1) how many rows are in the table
2) how many rows BY MONTH are in the table (i want to know how many rows for month one, month two
and so on)
3) what MONTH has the most rows (and for a special bonus, tell me why this question is ambiguous)
The fourth question is more of a "do something for me" - and that is "go to the white board, draw a picture of Oracle and tell me how it works".
As the link says - a surprising number of people *struggle* (seriously) with the first question. The second - gets *most* (seriously) of the rest. The third question freaks them out mostly. Especially the parenthetical part. The fourth question - sends people running out of the room.
That is why I liked the article I originally read - the author was poking around, developing derived requirements, fleshing it out, figuring out what really needed to be done, not rolling over and saying "you got it, I'll be right on it, we'll do that straight away". Developers (DBA's, whatever) that don't push back, that don't dig into the question, that don't try to convey "this is more complex than you think, we need to go a bit into this to figure out what you really need" - well, I don't have any patience for them. They do not belong (in our profession).
Will that person (the interview-e) annoy you? Sure, from time to time (I'm sure that every now and then - someone is annoyed by me, probably).
Will you ultimately be really happy they were there? Absolutely.
Will the person that rolls over annoy you? Absolutely - every time - most of the time probably. Especially after they really mess you up the first time they are so "flexible". Will you ultimately be even a little happy they were there? I doubt it.
I've said many times - there are only TWO answers to all technical questions. They are:
- WHY (why do you want to do that)
- IT DEPENDS (it really does, and it requires digging around, poking, probing to figure out what it depends on...)
poke, probe, ask, discuss, dive deep, play stupid (it works, really) - but get the information...
posted by Thomas Kyte at 9:16 PM
55 Comments:
1)
select count(*) from t;
2)
How'bout the year? You are looking for the current year's data or irrespective to the year?
3)
How'bout the year? You are looking for the current year's data or irrespective to the year?
Also, there could be more than one MONTH having the most number of rows. Do you want to report all the months or pick the first month in any particular order?
As far as the fourth question is concerned, I can draw and explain.
Hopefully, my answers are correct and you dont point me to the door. Am I eligible for other questions?
Wish I had an opportunity to work with you.
When I read that article the other day I was agreeing with the people providing negative feedback. I thought he went about it wrong.
But you have convinced me otherwise. He did answer it right, they were looking for the wrong answer.
It's the architects and developers that need critical thinking and should question "why" when appropriate.
1)
select count(*)
from t;
2)
select count(*) as
from t
group by month number;
3)
select count(*) as most_r;
from t
group by month number
order by most_r;
@anonymous #1
1) correct
2) I was not ambiguous there. I gave you a table with a column named MONTH and said "this has the number 1..12" in it, give me a count by month.
3) that is not why the question is ambiguous
@anonymous #2
1) correct
2) correct (well, the trailing number shouldn't be there but closer than close enough)
3) two problems with that answer. first - month is not selected, I don't know "which month" at all. Second, I only wanted the most frequently occurring month. You would display them all.
and you didn't address why the question - as phrased - is technically not finished, ambiguous. You cannot write code for it. No matter what query you write, I'll say "no, that is obviously not what I meant" :)
is 1=January or are you using accounting years from april-march so 1=April, or do you have a lookup table for monthnumber so they are not even sequential
I can tell you which month number has the most rows but not which month that is without some more information..
@Chris -
doesn't matter if 1=jan or not to answer the question. Month is just a number between 1 and 12.
a) how many rows in the table
b) how many rows by MONTH in the table
c) what is the most frequently occurring month.
Given that month is just a number between 1 and 12 - just looking for numbers to be reported out.
You can absolutely tell me which value of MONTH has the most rows (well, not really - I would be close to revealing the ambiguity if I say more :) )
It is just a number.
>why this question is ambiguous
Hmm...
Okay, the question sounds easy enough, and i'm sure i've been asked similar things before. So, i must be making a poor assumption:
1) Month means set of months.
2) Has the most rows, means has the most rows in this TABLE.
3) Having a row means just that, regardless of whether there is any data in it (other than the month)
None of those seems poor to me. I'm excited to know what i'm really missing.
@all
3) what MONTH has the most rows (and for a special bonus, tell me why this question is ambiguous)
Given the way the question is asked - how many rows is the person asking for this report expecting to see?
This is ambiguous in the same way "show me the top 3 paid people" is.
What if you had 300 employees.
And what if all 300 made the same amount of money?
What is the output of the report that shows you the top 3 paid people?
It is a problem endemic to the "show me the top-n" questions.
If I ask for "the top three earners by deptno" - should you use
row_number()
rank()
dense_rank()
All three are valid approaches - to *three entirely different questions*.
Without poking and probing and asking more questions - you would not know which of the three to use.
I believe most people mean "show me the SET of people by department that make the top three salaries" - that is
a) find the top three salaries by deptno
b) report out by deptno the set of people that make that much
meaning - there could be a section in the report with 1,000 rows in a deptno, another with 3, and another with 2.
Most people expect "3 rows per deptno" - until you point out the utter randomness of that.
The problem is that this guy basically says: "I'm so smart and you are a moron. You don't know what you're talking about." As he himself put it, he assailed. He was not being constructive. He didn't suggest a solution based on some assumptions, or two solutions based on different sets of assumptions.
Imagine that a boss says: "Build an intuitive GUI for this app." Instead of trying to make something intuitive and then maybe testing it on the secretary to make sure, he would say: "There's no such thing as generally intuitive. Some things are intuitive for kids, others are intuitive for adults, and no computer interface is intuitive for my 80-year-old grandma". Which may be true, but it's not a reason to release the app with an unusable GUI.
@Lev -
I wasn't in the interview, but I feel it was more like:
HR: give me the algorithm
him: wait, there are some questions
HR: give me the algorithm
him: but there are issues
HR: give me the algorithm
him: but I have to find out what to do
I can see how it dovetails into the "smart/moron" after the fact - basically, the interviewer wanted a canned response and wasn't necessarily read to have their world "questioned".
As for the intuitive GUI thing - I think you just proved the point. You make the point that you need push back and questioning.
Boss says "build an intuitive gui"
If you do what you suggest, build it, test it on the secretary, but the TARGET AUDIENCE WAS KIDS...
Well, you just wasted your time, the secretaries time, and the bosses money didn't you.
You should have said "intuitive is in the eye of the beholder - so who is the beholder in this case and have we any research that will help me build an intuitive one for that target audience - if not, I'll research it online and then prototype it. We'll need a test group representative of the target audience to ultimately test and refine it..."
Month is ambigious because if you have 5 years of data, you would have 5 January's.
To get the month with the "most" records, you would be summing up the records across all the years that January existed.
To get the max records of summed records. Perhaps something like
select max(records), month
from (select count(*) records, month
from some_table
group by month)
group by month
having max(records) in (select count(*) records
from some_table
group by month)
"Can return more than one row) so then just do a rownum <=1 at the end.
Of course, that is just nasty. You could also use rownum with a subquery that sorts descending and the main outer query doing a rownum <=1.
Analytic queries are much cleaner : ) But require more thought since I am not fluid in them.
Let me start working on one and I will post it!
Rather than fix my last one, this is a somewhat more simpler, but if you have multiple month with the same count, it does not help you there.
select * from
(select count(*), month
from some_table
group by month
order by month desc)
where rownum = 1
@anonymous
month is not ambiguous - I don't care about the year, that is why I just said "month", I want counts by month - period, regardless of year.
"Can return more than one row) so then just do a rownum <=1 at the end."
that is in fact the ambiguity, you as the developer should be pointing out to me - the requirements person - that what I asked for is not logical. Adding rownum <= 1 - that is not a valid solution if you ask me. It would make it such that the SAME set of data could return different answers to different people (it is not deterministic!)
And not only does your query return possibly more than one row (which is OK), it returns all of them
scott%ORA10GR1> with some_table as (select to_char(hiredate,'mm') month from emp)
2 select max(records), month
3 from (select count(*) records, month
4 from some_table
5 group by month)
6 group by month
7 having max(records) in (select count(*) records
8 from some_table
9 group by month)
10 /
MAX(RECORDS) MO
------------ --
1 01
2 02
2 04
2 05
1 06
2 09
1 11
3 12
8 rows selected.
Here would be a way to do it without analytics - using aggregates only:
scott%ORA10GR1> with some_table as (select to_char(hiredate,'mm') month from emp)
2 select month, count(*)
3 from some_table
4 group by month
5 having count(*) >= ALL(select count(*) from some_table group by month)
6 /
MO COUNT(*)
-- ----------
12 3
See this link to get more familiar with analytics for doing this stuff :)
Ooops
select * from
(select count(*), month
from some_table
group by month
order by 1 desc)
where rownum = 1
hmm.... answers really doesn't matters in this case. matter is how well the question is put up..........
So what's the it depends for your first technical question?
@forty2
Actually - for that one, the answer is "why - why are you counting records - stop doing that, you almost certainly do not need to"
Well, I knew I wouldn't pass an interview with you.
But I also know that after few months of unemployment there is no why? push back on that question. Besides, I thought you were against trick questions. If you want to test a candidate on their understanding of non-repeatable reads, then maybe asking how to count rows in a table is the wrong pass/fail criteria?
The programmer that rolls over and just answers the question - without enough information to actually answer the question - should send the interviewer running away. But that is apparently what a lot of interviewers are looking for.Yes, that is what most interviews are all about. A job interview is an unequal power relationship situation. Only the person conducting the interview can put that on an equal basis. Who decides if there was enough information on How many rows are in the table?. Should I ask if the database is Oracle, if the transaction is read-committed or serializable?
There really is enough information in that question to warrant an un-ambiguous answer. You want more, ask more.
There's another potential ambiguity in the first question too.
Unless you do a lock table you can't be sure that someone isn't inserting or deleting rows as you count them, so the answer you give may be out of date by the time it is produced.
Also, unless you are in a read only transaction you don't know whether your own session has uncommitted rows in the table.
So a better answer to the first question may be
select count(*) from t as of timestamp .....
In that you get the count of committed records at a specific time (and ignore any uncommitted changes your session may have made).
@Gary
I don't agree
Oracle has this thing called read consistency
The results of any query will be "as of the time the query was submitted" - every time by default.
It is not out of date, it is simply "as of the time the query began, when you submitted it"
As for the rows you added, according to the way relational databases *must* work, there is no ambiguity there - you will count them ( the rows you modified )
the ambiguity is not in the way Oracle the database processes the data - the ambiguity is in the question (#3) itself. It seems very straightforward - but it is not
Gary,
There's another potential ambiguity in the first question too.That will apply to #2, #3 and any other query one might ask.
There is nothing wrong with leading the discussion to concurrency and read consistency ... to expect it to get there seems a bit of a stretch.
Some things just do not need to be made bigger, or more complex than they are. That is where the fellow with the interview likely stepped wrong.
That's part of my point. Tom's comment at 03:32 was "why do you want the count". Mine is an extension, saying, do you want to count uncommitted changes or not. The flashback query ONLY returns committed records and won't include any changes your session has made (even if they were made before the flashback timestamp).
select count(*) from TABLE as of timestamp systimestamp;
will always show the current count of committed records.
Any session that runs an AS OF query gets exactly the same result. It is the sort of answer auditors like :)
Given the questions' phrasing, and the person asking, I'm surprised at the laxness of acceptance. I'd consider answers for #2 and #3 thus far to be incorrect.
2) how many rows BY MONTH are in the table (i want to know how many rows for month one, month two and so on)
3) what MONTH has the most rows (and for a special bonus, tell me why this question is ambigous)
I would've thought you were looking for:
WITH ALL_MONTHS AS
(SELECT LEVEL MONTH FROM DUAL CONNECT BY LEVEL < 13)
SELECT ALL_MONTHS.MONTH, SUM(DECODE(T.MONTH,NULL,0,1))
FROM ALL_MONTHS, T
WHERE ALL_MONTHS.MONTH = T.MONTH(+)
GROUP BY ALL_MONTHS.MONTH;
And for #3:
WITH ALL_MONTHS AS
(SELECT LEVEL MONTH FROM DUAL CONNECT BY LEVEL < 13),
ALL_MONTH_COUNTS AS
(SELECT ALL_MONTHS.MONTH, SUM(DECODE(T.MONTH,NULL,0,1)) NUM_ROWS
FROM ALL_MONTHS, T
WHERE ALL_MONTHS.MONTH = T.MONTH(+)
GROUP BY ALL_MONTHS.MONTH)
SELECT *
FROM ALL_MONTH_COUNTS
WHERE NUM_ROWS =
(SELECT MAX(NUM_ROWS) FROM ALL_MONTH_COUNTS);
@Kevin
I disagree with your assessment for #2. I just wanted data for what is in there.
But you know what - if you were in an interview with me, and you asked that question (reasonable question to ask - definitely) - you'd have gotten bonus points.
But, I would have pointed out that your query should have been simply:
WITH ALL_MONTHS AS (SELECT LEVEL MONTH FROM DUAL CONNECT BY LEVEL < 13),
t as (select to_char(hiredate,'mm') month from emp)
SELECT ALL_MONTHS.MONTH, count(t.month)
FROM ALL_MONTHS, T
WHERE ALL_MONTHS.MONTH = T.MONTH(+)
GROUP BY ALL_MONTHS.MONTH;
count(t.month) would be the right approach, yours works, but isn't really saying what you want to do.
Kevin,
And you know MONTH is integer ... how? Just because the column is spelled MONTH.
What if I were to tell you the column represents fiscal months and month 13 is represented as 4.2?
And if one assumes MONTH represents calendar months how about going back asking about proper data types, a foreign key or check constraints, the Full Monty?
I can be as picky as the next bloke. By the time one gets here it just a coin toss … obsessive pedantry or critical thinking?
Interviews are subjective.
@forty2
Well, in the particular case, Kevin knows about the "MONTH" column because this started with:
I have a table:
create table t ( ....., month number, ..... );
Month is always a number between 1 and 12.
Anonymous,
See, simple question, unnecessarily complicated on incomplete assumptions ... just got you flunking the interview.
SQL> create table f ( month number check (month between 1 and 12);
Table created.
...snip...snip...
SQL> select count(*) from f;
COUNT(*)
----------
42SQL> WITH ALL_MONTHS AS
2 (SELECT LEVEL MONTH FROM DUAL CONNECT BY LEVEL < 13)
3 SELECT ALL_MONTHS.MONTH, SUM(DECODE(T.MONTH,NULL,0,1))
4 FROM ALL_MONTHS, F T
5 WHERE ALL_MONTHS.MONTH = T.MONTH(+)
6 GROUP BY rollup(ALL_MONTHS.MONTH);
MONTH SUM(DECODE(T.MONTH,NULL,0,1))
---------- -----------------------------
1 0
2 0
3 0
4 0
5 0
6 0
7 0
8 0
9 0
10 0
11 0
12 0
0 <=== !?!?!
13 rows selected.
SQL>
You just put a bug in my code. Column names are not constraints.
I'll admit it, I struggled with question 1! But it was not because I didn't know how to write:
SELECT count(*) FROM t;
It was because a SQL statement is not an answer to the question "How many rows are in the table?" Instead, SQL is a means by which you find an answer. The way the question is worded it seems to expect a direct answer. I'm not trying to be "smart" here... Honestly, the way the question is worded (and maybe in the context of "interview questions/puzzles") made me at first, think there was something "hidden" in the setup of the question where you can somehow deduce, if not a numeric answer, at least some sort of rule statement or deduction in English. Maybe that's why some of the people who struggle with that question, really do struggle with it? If instead you asked "How would you determine the number of rows in the table?", the possibility of SQL being an acceptable answer may dawn on them much quicker? ... but then again it's a Friday night and I probably stopped thinking hours ago.
hello... you may submit this blog to my webBlog Directory, keyworddir.info.. have a nice day!
Keyword Directory
Tom,
In this question- why is a DATE field being considered to be stored as a number(MONTH)in the table?
Shouldnt one store the month as a DATE data type( probably trunc'ing to the first of the month)
My guess is that the number is stored for the ease of neglecting the "year" portion when grouping by to get the answer.
-George Joseph
@George,
that question might not have been relevant. You were told month is a number, between 1 and 12 - we would like a count of records by month.
However, I could have responded with "we wanted to partition the data by month and this was before 11g with virtual columns"
Or, this table is really a view and month is to_char( date, 'mm' ) month.
Or, we just store data by month, regardless of the year - we were looking for seasonality - not by year - it is what we needed. The table does not store the year at all.
Could it be a red flag of an incorrect design? Yes.
@Tom,
Fair enough, and in an interview setting, I'd drop the point instantly. However, this being a blog comment :-) , I'll leave one parting shot. A query which returns no count for "month one", given a requirement that explicitly requests the count for "month one", is not a correct query, IMO.
As per the count(month) vs. sum(decode(...)); I'll concede the point. In my defense, the behavior of NULLs within SQL functions is often non-intuitive; I'm always 99% sure of my recollection that SQL aggregators discard NULLs, but whenever I encounter something like "count(month)", I have to rip off a quick:
SELECT count(c1) FROM (
select 1 c1 FROM DUAL UNION ALL
SELECT NULL C1 FROM DUAL);
My personal preference for constructs like "SUM(DECODE(...))" is their transparent, albeit less-than-intuitive, treatment of NULLs.
@forty2
"And you know MONTH is integer ... how? Just because the column is spelled MONTH. What if I were to tell you the column represents fiscal months and month 13 is represented as 4.2?"
Well, no.
I know it is an integer because
1) "MONTH" was described as a number "between 1 and 12" (not a number "between 1.0 and 12.0").
2) MONTH was subsequently refered to as a series: "i want to know how many rows for month one, month two and so on.".
With those two cues, Tom has left no ambiguity; the domain of MONTH is firmly established as the set of integers {1, 2, ... 12}.
"And if one assumes MONTH represents calendar months how about going back asking about proper data types, a foreign key or check constraints, the Full Monty?"
Because the question is not about table design, it's about writing a query. To borrow your phrase, this response would constitute 'obsessive pedantry'
"I can be as picky as the next bloke. By the time one gets here it just a coin toss … obsessive pedantry or critical thinking?"
I disagree about it being a coin toss. I think "questioning the question" can demonstrate attention to detail and critical thinking skills without looking like 'obsessive pedantry'. IMO, 'obsessive pedantry' is easily spotted, and often belies a desire to distract from the interviewee's inability to answer the question at hand.
> Could it be a red flag of an incorrect design? Yes.
OMG, a few years after finally figuring out how to be a good interviewee, I started going downhill when just about everything I was asked would raise red flags like this. It's almost better to be asked "the person you would be replacing wiped out the production database, what do you think about that?" (Which I have been - and apparently going wide-eyed then immediately bouncing back was part of the right answer, I don't remember what else I said).
word: repint
They asked a question which was irrelevant to the job. I would leave right way. I do not want to work with or for someone having no idea what thay were going to hire. The people in the Hr and the hiring organization of the company are Not on the page. That is not a heathy company.
@Sean:
I have seen very few healthy companies by that measure.
It is as difficult to evaluate how bad a company is from the interview as it is to interview some stranger for the company. Yes, you can see red flags, even make correct predictions about problems to be seen. But in reality, it's been my observation that the overall predictiveness either way is relatively low - there is often little relationship of what the job really entails v. what the spec says, and companies may have many microenvironments which may change suddenly. In other words, interviewing isn't necessarily all that useful.
I know I've seen several generations of HR people while I've had my current job, with quite varying styles. Yet upper management hasn't changed at all, and still have little clue about IS. And we all perhaps have skewed ideas of what would make a good CEO.One man's hell may be another's opportunity. I know my wants and needs from a company have changed markedly from when I was a junior, even though my basic personality hasn't much. One thing I have learned is that if I know way more than my direct boss, it's not too difficult to leverage that to value - the hard part is not making him or her feel stupid. I also am a firm believer that a group should have a range of skill and experience, otherwise you just get a bunch of index-rebuilders.
word: erswic
Joel,
Thanks for sharing your thoughts.
A couple of things brother me 1) in the first place, Why the guy seemed having the strong hands-on technical background did go for the interview of technical writer? 2) Why did the interviewer ask such non relevant question if he/she knew what the position to be filled? I do not believe the interviewers did not know what the position was.
1) Seems the guy did not know what he was going to do. I would not hire someone like him for the position, because I know he will not be doing the job for long. Heck I want to hire a tech writer. 2) The interviewers would be your co-workers or supervisors if you were hired. Any sound reasons for asking a tech writer those kind of the questions? No. Those people were nuts. I would run to door.
It was not about how good the he answered the question which nothing to do with the job.
The whole thing from his going the interview and to the questions were kind of jokes.
Sean
Sean
Sean:
Isn't it possible the technical writer position required this knowledge? Note that both Tom and the original poster Kas thought it was an excellent question, Kas was asked it from one of 5 interviewers and questioned whether it was appropriate - but looking at if from the other side, perhaps the interviewers also either thought the guy might be more appropriate for a developer/designer, or thought he was/should have been wanting to get in the door and then sidle over to something more appropriate.
I know I took a support position once, after being a hardcore designer/developer, but it was a more technical position than one would normally think of as a support position and was actually pretty cool and appropriate. It is pretty common for positions to mutate to the skills of then person in the position, especially when the coworkers go "holy moley, this guy's good."
There have been a couple of times when someone from Oracle support has been particularly good, and when I press them on it, find out they've been DBA's or whatever. Sometimes when you have kids or medical issues you just want stability over perfection in your job.
I also note Tom said "...seemed inappropriate...," obviously we have to be careful about what we assume. But then again, I've seen more situations where you are absolutely right. The real moral is to keep your eyes open and don't jump to conclusions.
word: lectol
One of my favorite interview question is: "What is the difference between an inner and an outer join"
My experience is similar to Tom's. People who rate themselves as good in SQL fail on that one.
The most "frustrating" answer I once got was: "Outer join is, if you are joining more than one table"...
I poked a bit to see if he just couldn't express correctly what he wanted to tell, but no. He stuck to that answer.
your article was very interstinh..keep on posting such articles sothat we get to know about certain things other than oracle..
LOL! I felt like a moron initially, because my question in response to question 1 was "I don't know, have you loaded any rows in it yet? You only gave me a (partial) create statement :)"
My company has been interviewing for an Oracle developer position the past few weeks and I have been using these questions. It is ASTOUNDING how few people answer in any constructive way without extensive coaching. I feel these questions are very effective and will continue to use them.
1) select count(*)
from t;
2) select month, count(month)
from t
group by month
order by month ;
3) select b.month
from (select max(count(month)) maxcnt from t group by month) a,
(select month, count(month) mthcnt from t group by month) b
where a.maxcnt = b.mthcnt;
What the interviewee should have answered back straight away (after a bit of silent thinking) is: "Am I getting tested on developing a methodology for sampling English texts or am I getting tested for algorithm design?".
Although I fully agree that questioning everything is a good idea during normal work, one could get a feeling that the interviewee may be trying to evade the test of skills which the interviewer is interested in.
Lastly, I'm not too impressed with his hash table design and talking about 64GB of RAM... It's such a Java developer answer... :(
scott@10G> SELECT COUNT(*) FROM T;
COUNT(*)
----------
78
scott@10G> SELECT MONTH_NUMBER,COUNT(*) AS MONTH_COUNT
2 FROM T
3 GROUP BY MONTH_NUMBER;
MONTH_NUMBER MONTH_COUNT
------------ -----------
1 12
6 7
11 2
2 11
4 9
5 8
8 5
3 10
7 6
9 4
10 3
12 1
12 rows selected.
scott@10G> SELECT T1.month_number,COUNT(*) AS MAX_MONTH_COUNT
2 FROM T T1
3 GROUP BY T1.month_number
4 HAVING COUNT(*) = (SELECT MAX(COUNT(*)) FROM T T2 GROUP BY T2.month_number)
5 /
MONTH_NUMBER MAX_MONTH_COUNT
------------ ---------------
1 12
@Rajesh > I would have written the last query this way :
SELECT MAX(T1.month_number) KEEP (DENSE_RANK FIRST ORDER BY COUNT(*) DESC) AS MONTH_NUMBER,
MAX(COUNT(*)) AS MAX_MONTH_COUNT
FROM T T1
GROUP BY T1.month_number
@Fabien
well, then I would have hired Rajesh and rejected you.
Because your answer is *wrong*, especially in light of the fact that you got to read all of the other comments and you know what the 'ambiguity' was.
ops$tkyte%ORA11GR1> create table t ( month_number number );
Table created.
ops$tkyte%ORA11GR1> insert into t select level l from dual connect by level <= 12;
12 rows created.
ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> SELECT MAX(T1.month_number) KEEP (DENSE_RANK FIRST ORDER BY COUNT(*) DESC) AS MONTH_NUMBER,
2 MAX(COUNT(*)) AS MAX_MONTH_COUNT
3 FROM T T1
4 GROUP BY T1.month_number
5 /
MONTH_NUMBER MAX_MONTH_COUNT
------------ ---------------
12 1
ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> SELECT T1.month_number,COUNT(*) AS MAX_MONTH_COUNT
2 FROM T T1
3 GROUP BY T1.month_number
4 HAVING COUNT(*) = (SELECT MAX(COUNT(*)) FROM T T2 GROUP BY T2.month_number)
5 /
MONTH_NUMBER MAX_MONTH_COUNT
------------ ---------------
1 1
6 1
11 1
2 1
4 1
5 1
8 1
3 1
7 1
9 1
10 1
12 1
12 rows selected.
Your query returns *a record*.
The correct answer is a set of records - anywhere from zero to 12.
Computers are useless. They can only give you answers.
- Pablo Picasso
This is how I would write the query:
select month_number from
(
select month_number, rank() over (order by cnt desc) rnk from
(
select month_number, count(*) cnt from t group by month_number
)
)
where rnk = 1;
Hi Tom,
Please tell the best possible way to master oracle as a developer and also as a DBA.
Thanks in Advance,
Suresh
Hi Tom,
Can you please suggest the approach to become a really good oracle programmer and also Oracle DBA.
Thanks in Advance,
Suresh B G
@Suresh B G
see these articles...
select month, count(month) from t
group by month
having count(month) >= (select max(count(month)) from t group by month)
@anonymous
sure, it works - but - I would tease you for the >=
how could the count(*) be GREATER than the max(count(*)) ?
sir ..i want select 1st records from any query . plz help me?
@kiran
select * from (ANY_QUERY) where rownum = 1;
POST A COMMENT
<< Home