Home » SQL & PL/SQL » SQL & PL/SQL » How to sort this?
How to sort this? [message #316188] Thu, 24 April 2008 03:31 Go to next message
wakula
Messages: 150
Registered: February 2008
Location: Poland
Senior Member
Hello,
I would like to sort the following table:

Class, Grade, Name
A, 5, John Smith
A, 2, Johny Bravo
A, 4, Mark Spencer
A, 4, Will Smith
B, 5, Billy Dean
B, 5, Donald Duck
B, 5, Jane Lou
B, 5, Mary Jane
C, 3, George Spencer
C, 3, Joanne d'Arc
C, 2, Richard the Great
C, 4, Steve Kovalsky

to get order like this:

Class, Grade, Name
B, 5, Billy Dean
B, 5, Donald Duck
B, 5, Jane Lou
B, 5, Mary Jane
A, 5, John Smith
A, 2, Johny Bravo
A, 4, Mark Spencer
A, 4, Will Smith
C, 3, George Spencer
C, 3, Joanne d'Arc
C, 2, Richard the Great
C, 4, Steve Kovalsky

where:
B is first, as it has highest grades
C is last, as it has worst grades
Within a class names are sorted alphabetically
Results are sorted 'per class', but 'classes' are sorted

How can I do this?
Is it possible?

Thanks in advance.
Re: How to sort this? [message #316190 is a reply to message #316188] Thu, 24 April 2008 03:34 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Use ORDER BY (DECODE (grade, 'B', 1, 'A', 2, 'C', 3, etc.))

Re: How to sort this? [message #316193 is a reply to message #316190] Thu, 24 April 2008 03:45 Go to previous messageGo to next message
wakula
Messages: 150
Registered: February 2008
Location: Poland
Senior Member
This is not what I expect.
I don't want to have a fixed sorting 'B first, A second and C last'. And in the answer from Littlefoot 'grade' is not 'A','B','C'.

To extend the description:
sort by sum(grades of class) is made
results are grouped by class
Re: How to sort this? [message #316201 is a reply to message #316193] Thu, 24 April 2008 04:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I you post a test case and format your post as requested in OraFAQ Forum Guide, I can show you how to do it.

Regards
Michel
Re: How to sort this? [message #316207 is a reply to message #316193] Thu, 24 April 2008 04:54 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Hm, right; obviously, I didn't pay enough attention to your question.

The answer is in ordering contents of the table by analytical form of the SUM function (grade summary), partitioned by class.
Re: How to sort this? [message #316214 is a reply to message #316188] Thu, 24 April 2008 05:09 Go to previous messageGo to next message
kecd_deepak
Messages: 52
Registered: December 2007
Member
Hello Littlefoot,
Can you please give more Hint for the solution.

thanks
Re: How to sort this? [message #316215 is a reply to message #316214] Thu, 24 April 2008 05:18 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
I think he has already given you a hint
Quote:
analytical form of the SUM function (grade summary), partitioned by class

Regards

Raj
Re: How to sort this? [message #316218 is a reply to message #316215] Thu, 24 April 2008 05:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
S.Rajaram wrote on Thu, 24 April 2008 12:18
I think he has already given you a hint
Quote:
analytical form of the SUM function (grade summary), partitioned by class

Regards

Raj

I think you have to remove "more Hint for" in OP answer to clearly understand it.

Regards
Michel

Re: How to sort this? [message #316240 is a reply to message #316214] Thu, 24 April 2008 06:29 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
kecd_deepak wrote on Thu, 24 April 2008 12:09
Hello Littlefoot,
Can you please give more Hint for the solution.

thanks


I could, but I will not and, as the next step is, actually, a solution, it would be pointless. Now you know what to search for, so - do that effort. Write the query by yourself and post it here if you feel that it doesn't produce a desired output. I'm sure someone will help you fix it.
Re: How to sort this? [message #316242 is a reply to message #316188] Thu, 24 April 2008 06:46 Go to previous messageGo to next message
wakula
Messages: 150
Registered: February 2008
Location: Poland
Senior Member
Thanks for your answers. I will now start googling about it Smile
Re: How to sort this? [message #316250 is a reply to message #316242] Thu, 24 April 2008 07:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No start working on it, you will lose less time and learn much more.

Regards
Michel
Re: How to sort this? [message #316261 is a reply to message #316188] Thu, 24 April 2008 07:11 Go to previous messageGo to next message
kecd_deepak
Messages: 52
Registered: December 2007
Member
Thanks Littlefoot,
I solve the problem with this hint.
actually this is new concept for me.

Thanks & regards
Deepak
Re: How to sort this? [message #316262 is a reply to message #316261] Thu, 24 April 2008 07:13 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Fine. Now, please, post the solution so that other Forum members might benefit from it.
Re: How to sort this? [message #316264 is a reply to message #316188] Thu, 24 April 2008 07:16 Go to previous messageGo to next message
wakula
Messages: 150
Registered: February 2008
Location: Poland
Senior Member
One question: does "analytical form of..." mean that "PL/SQL function" needs to be created?

If so, then I'm in troubles - as this is some example used to show what needs to be done - and there is a complicated SQL query (long for more than 10000 characters) being executed from PL/SQL code to copy data from several tables into one destination table. In fact - PL/SQL is not available for the interface, where I should implement this.
Re: How to sort this? [message #316267 is a reply to message #316188] Thu, 24 April 2008 07:25 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
No, analytic functions are built-in SQL functions (at least from version 8.1.7). To know them, you shall read the documentation, found eg. online on http://tahiti.oracle.com/

SQL Reference
Chapter 5 Functions
SQL Functions
Analytic Functions
Re: How to sort this? [message #316270 is a reply to message #316267] Thu, 24 April 2008 07:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
at least from version 8.1.7

8.1.6

Regards
Michel
Re: How to sort this? [message #316289 is a reply to message #316188] Thu, 24 April 2008 08:38 Go to previous messageGo to next message
wakula
Messages: 150
Registered: February 2008
Location: Poland
Senior Member
Thanks for your help,
I have found some solution.
Keep in mind that it is not the best solution and is several times slower than analytic functions.
select Class, Grade, Name
from MyTable tab_in
order by
 (
 select r
  (
  select ROWNUM r, Class from 
   (
   select Class
   from MyTable
   group by Class
   order by sum(Grade) DESC, Class
   )
  ) tab_select
 where tab_in.Class = tab_select.Class
 ),
 tab_in.Name;


So assuming
nc = number of Class'es
we have following steps:
1) Calculate sums of grades for every class. Result is a table with nc rows (and Class for results identification)
2) We need row numbers from previous query (as it cannot be mad in "group by" expression - we have to make another step for this), but we want to keep Class for the row identification
3) We need to find what is the position of a data row in previously created table, but this must be done in additional step - else we would get a single row with "where" caluse
4) Within a Class we want to sort by Name

Regards

[Updated on: Fri, 25 April 2008 03:20]

Report message to a moderator

Re: How to sort this? [message #316296 is a reply to message #316289] Thu, 24 April 2008 09:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
LittleFoot gave you a better idea.

Regards
Michel
Re: How to sort this? [message #316305 is a reply to message #316289] Thu, 24 April 2008 09:55 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
O.K I will give you a partial solution. All you have to do is fill up the blanks

SQL> col sum_val noprint
SQL> l
  1  with t
  2  as
  3  (select 'A' class, 5 grade, 'John Smith' name from dual union all
  4  select 'A', 2, 'Johny Bravo' from dual union all
  5  select 'A', 4, 'Mark Spencer' from dual union all
  6  select 'A', 4, 'Will Smith' from dual union all
  7  select 'B', 5, 'Billy Dean' from dual union all
  8  select 'B', 5, 'Donald Duck' from dual union all
  9  select 'B', 5, 'Jane Lou' from dual union all
 10  select 'B', 5, 'Mary Jane' from dual union all
 11  select 'C', 3, 'George Spencer' from dual union all
 12  select 'C', 3, 'Joanne d''Arc' from dual union all
 13  select 'C', 2, 'Richard the Great' from dual union all
 14  select 'C', 4, 'Steve Kovalsky' from dual
 15  )
 16  select class, grade, name, <fill_up_the_blank> sum_val from t
 17* order by sum_val desc, name
SQL> /

C      GRADE NAME
- ---------- -----------------
B          5 Billy Dean
B          5 Donald Duck
B          5 Jane Lou
B          5 Mary Jane
A          5 John Smith
A          2 Johny Bravo
A          4 Mark Spencer
A          4 Will Smith
C          3 George Spencer
C          3 Joanne d'Arc
C          2 Richard the Great
C          4 Steve Kovalsky

12 rows selected.

Regards

Raj
Re: How to sort this? [message #316306 is a reply to message #316188] Thu, 24 April 2008 09:58 Go to previous messageGo to next message
wakula
Messages: 150
Registered: February 2008
Location: Poland
Senior Member
Thanks,
I'm currently reading this page and I'll get back to it tomorrow.
Re: How to sort this? [message #316391 is a reply to message #316188] Thu, 24 April 2008 23:12 Go to previous messageGo to next message
kecd_deepak
Messages: 52
Registered: December 2007
Member
My solution is following:

 select a.class,a.grade,a.name,
        (select sum(grade) from dj_faq where class=a.class) as total 
from dj_faq a 
order by total desc,class 


Regards
Deepak

[Updated on: Thu, 24 April 2008 23:27] by Moderator

Report message to a moderator

Re: How to sort this? [message #316394 is a reply to message #316391] Thu, 24 April 2008 23:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Still not read the page and try to use analytics.

Regards
Michel
Re: How to sort this? [message #316399 is a reply to message #316188] Fri, 25 April 2008 00:06 Go to previous messageGo to next message
kecd_deepak
Messages: 52
Registered: December 2007
Member
Hello
I think this querry use analytic function.

SELECT class,grade,name ,SUM(grade) OVER (PARTITION BY class) total FROM dj_faq ORDER BY total DESC 


Regards
Deepak
icon14.gif  Re: How to sort this? [message #316449 is a reply to message #316188] Fri, 25 April 2008 02:55 Go to previous messageGo to next message
wakula
Messages: 150
Registered: February 2008
Location: Poland
Senior Member
I think that the one above is the correct answer.
At least it works for me and is several times faster than solution created by me.
As requested - I have removed the final solution from my post.
And since it is not so easy to find the correct information how to do it, here is a link to the needed information: http://www.psoug.org/reference/analytic_functions.html

P.S.
This was not a homework and in fact my full query is about 20000 characters long.

[Updated on: Fri, 25 April 2008 03:12]

Report message to a moderator

Re: How to sort this? [message #316453 is a reply to message #316449] Fri, 25 April 2008 03:03 Go to previous message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Too bad you didn't work on it and find it by yourself.

@kecd_deepak,
Don't put solution only hint or clue as requested in OraFAQ Forum Guide, "Responding to Posts" section:
Quote:
When responding to questions, if it is obviously a student with a homework assignment or someone else just learning, especially in the homework and newbies forums, it is usual to provide hints or clues, perhaps links to relevant portions of the documentation, or a similar example, to point them in the right direction so that they will research and experiment on their own and learn, and not provide complete solutions to problems. In cases where someone has a real-life complex work problem, or at least it may seem complex to them, it may be best to provide a complete demo and explanation.


Regards
Michel

Previous Topic: pl/sql code for a VIEW
Next Topic: Outer join with variable
Goto Forum:
  


Current Time: Wed Dec 07 10:23:59 CST 2016

Total time taken to generate the page: 0.19933 seconds