Home » SQL & PL/SQL » SQL & PL/SQL » TYPICAL QUESTION (ORACLE 10G)
TYPICAL QUESTION [message #350304] Wed, 24 September 2008 12:28 Go to next message
srinivas.k2005
Messages: 342
Registered: August 2006
Senior Member
Hi,

i had an interview and they asked a question like:
i have a table with one column of number datatype with 1 to 10 as values.

how will you display teh values in desc order without using the DESC built in.

and the method shoul be applicable for any datatype, i.e., need to show records in desc order with out using DESC built in.

Please let me know if anybody has a good idea.
I tried with DECODE IN ORDER BY AND DI NOT WORK.

--SRINIVAS
Re: TYPICAL QUESTION [message #350306 is a reply to message #350304] Wed, 24 September 2008 12:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
TYPICAL ANSWER: order by -1*col.

Regards
Michel

[Updated on: Wed, 24 September 2008 12:30]

Report message to a moderator

Re: TYPICAL QUESTION [message #350316 is a reply to message #350306] Wed, 24 September 2008 13:19 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Michel Cadot wrote on Wed, 24 September 2008 18:29
TYPICAL ANSWER: order by -1*col.

Regards
Michel



SQL> select last_name, salary
  2  from employees
  3  order by -1*last_name;
select last_name, salary
       *
ERROR at line 1:
ORA-01722: invalid number


My answer? I wouldn't, I would use DESC, that's what it's there for. I wouldn't waste my time trying to find a pointless solution to a problem that has a ready made answer. I would consider answering their question with another question:
I have a car. It runs on petrol. I have a plentiful supply of petrol, but I am an awkward git and I want to use hydrogen instead. How would you make this possible?
Re: TYPICAL QUESTION [message #350318 is a reply to message #350316] Wed, 24 September 2008 13:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
have a table with one column of number datatype

I doubt you can put last_name in a number datatype. Wink

It is just an interview to see how candidate reacts. The answer to the question is of no interest.

Regards
Michel
Re: TYPICAL QUESTION [message #350319 is a reply to message #350318] Wed, 24 September 2008 13:33 Go to previous messageGo to next message
srinivas.k2005
Messages: 342
Registered: August 2006
Senior Member
Yes i need to know for any datatype, may be it is varchar .
Any ideas?
Re: TYPICAL QUESTION [message #350321 is a reply to message #350319] Wed, 24 September 2008 13:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Yes i need to know for any datatype

Why?

Regards
Michel
Re: TYPICAL QUESTION [message #350326 is a reply to message #350304] Wed, 24 September 2008 14:32 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
I look forward to someone giving a useful answer to this question.

Let me remind everyone that oracle has said over and over:

Quote:
"Oracle does not guarantee the order of rows returned from a query, unless an ORDER BY is used"

So... for the creative out there that are contemplating a solution that does not use order by, you are wrong.

Most of us can find at least one way to make this work. Heck if nothing else you could always create an object_type or function that is an INVERSE function for the datatypes and then sort on that. But under what conditions would that be superior to DESC?

Good luck, Kevin
Re: TYPICAL QUESTION [message #350329 is a reply to message #350304] Wed, 24 September 2008 14:40 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Is this cheating? Should work for any datatype...

MYDBA@orcl > start cheat;
MYDBA@orcl >
MYDBA@orcl > create table bogus(a) as select level from dual connect by level <= 10;

Table created.

MYDBA@orcl >
MYDBA@orcl > select a from bogus order by a;

         A
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

10 rows selected.

MYDBA@orcl >
MYDBA@orcl > select a
  2  from
  3  (
  4          select a, row_number() over (order by a desc) r
  5          from bogus
  6  )
  7  order by r;

         A
----------
        10
         9
         8
         7
         6
         5
         4
         3
         2
         1

10 rows selected.

MYDBA@orcl >
MYDBA@orcl > drop table bogus;

Table dropped.

MYDBA@orcl > set echo off
MYDBA@orcl >

Re: TYPICAL QUESTION [message #350331 is a reply to message #350304] Wed, 24 September 2008 14:45 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Another option might involve use of ascii codes.

Or another option might be to turn your rows into a single, delimited string, use the reverse built in function on that string, then turn that string back into row output.

MYDBA@orcl > select reverse('1,2,3') from dual;

REVER
-----
3,2,1

1 row selected.
Re: TYPICAL QUESTION [message #350338 is a reply to message #350331] Wed, 24 September 2008 15:20 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
smartin wrote on Wed, 24 September 2008 15:45
Another option might involve use of ascii codes.



Oh crapenstein, I was going to suggest that
order by 1000 - ascii(substr(col1,1))
Re: TYPICAL QUESTION [message #350354 is a reply to message #350318] Wed, 24 September 2008 16:46 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Michel Cadot wrote on Wed, 24 September 2008 19:29
Quote:
have a table with one column of number datatype

I doubt you can put last_name in a number datatype. Wink

Regards
Michel


Quote:
and the method shoul be applicable for any datatype


Quote:

It is just an interview to see how candidate reacts. The answer to the question is of no interest.


It's rare that I would say this to you Michel, but;
Nonsense, I couldn't disagree more. The answer that the interviwee gives is how he/she reacts. If I was interviewing someone, the only reason I would ask this question would be to see if they had the sense to say "Why on earth would you want to? Is there some level of complexity that you are not telling me about?". I'm looking for someone that will take the most efficient route from A to B. Anyone wasting their time on trying to work out an actual solution is of little use to me. Be creative when creativity is called for, but more importantly, use the right tool for the right job. To order a set of results in reverse order i.e. from highest to lowest, use ORDER BY col DESC. end of story. Now if you want to add to, and complicate the question so that a simple ORDER BY col DESC will not actually get the required result, then that becomes a different story, but that is not the question that was asked.
Re: TYPICAL QUESTION [message #350434 is a reply to message #350354] Thu, 25 September 2008 01:05 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Nonsense, I couldn't disagree more. The answer that the interviwee gives is how he/she reacts. If I was interviewing someone, the only reason I would ask this question would be to see if they had the sense to say "Why on earth would you want to? Is there some level of complexity that you are not telling me about?". I'm looking for someone that will take the most efficient route from A to B. Anyone wasting their time on trying to work out an actual solution is of little use to me. Be creative when creativity is called for, but more importantly, use the right tool for the right job. To order a set of results in reverse order i.e. from highest to lowest, use ORDER BY col DESC. end of story. Now if you want to add to, and complicate the question so that a simple ORDER BY col DESC will not actually get the required result, then that becomes a different story, but that is not the question that was asked.

We are not in disagreement, this is what I repeat in almost all the interview questions that seem silly and are posted in forum (here and elswhere).
Say I just give the second part of answer, after yours and when interviewer stress to get a direct answer to the question (some really post silly questions).

Regards
Michel
Previous Topic: To select the rows, for which most of the columns have values
Next Topic: How to change unconditional select order result (merged)
Goto Forum:
  


Current Time: Fri Dec 09 23:14:25 CST 2016

Total time taken to generate the page: 0.10993 seconds