CODEWITHSUNDEEP
sqloracle-database
sonic boom
sonic boom

Reputation: 904

Order oracle query result in sequence as of OR clauses

I have an oracle table with structure something like:

School {
 Student_Id,
 Student_Name,
 Class,
 Sport,
 Club
}

I want to write a query to fetch all students who either belong to X class, or Y Sport, or Z club.

But I want to order the result based on the sequence of my OR conditions.

That is all students belonging to X class will come first, before the students of Y sport. Then students of Z club will come.

Also, no duplicate results. That is if John is from class X & also belongs to sport Y, then he should only be only appear once and on top of all results of sports Y.

Upvotes: 0

Views: 49

Answers (3)

Gabriel Durac
Gabriel Durac

Reputation: 2760

select student_id
, case when class='X' then 1
       when sport = 'Y' then 2 
       when club='Z' then 3
       else 4
  end as Ordr
from School 
WHERE class='X' or sport = 'Y' or club='Z'
Order by ordr

Explanation:

The WHERE class='X' or sport = 'Y' or club='Z' simply implements your desired filtering.

The custom ordering is implemented by creating an additional column called ordr and using it in the ORDER BY clause. This column is created using a case statement. The order of the how this is written is important because a CASE expression evaluates to the first true condition and, if there is no true condition, it evaluates to the ELSE part.

So all students taking class X will get an ordr of 1, regardless of their sport and club.

If a student does not take class X, the expression will try to evaluate if the student takes sport Y and if that is the case, that student will get assigned an ordr of 2 regardless other column values.

Then if the student does not take either class X nor sport Y, the case expression will check if the student is in club Z. If that is true he will be asigned an ordr of 3.

Lastly, if a student is neither in class X, does sport Y or is in club Z, he wil get assigned an ordr of 4.

ORDER BY is ASCENDING by degfault meaning 1 will show up before 2 and so on.

What this algorithm does not do, which you can tell by the above explanation, is prioritize a student that takes class X, sport Y, club Z over someone that only takes class X.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269763

I would write this as:

select s.*
from school s
where class = 'X' or sport = 'Y' or club = 'Z'
order by (case when class = 'X' then 1
               when sport = 'Y' then 2
               when club = 'Z' then 3
          end)

If you don't want to repeat the conditions, you can use a subquery, CTE, or -- in Oracle 12C -- a lateral join:

select s.*
from school s cross join lateral
     (select (case when class = 'X' then 1
                   when sport = 'Y' then 2
                   when club = 'Z' then 3
              end) as ord
      from dual
     ) x
where x.ord is not null
order by x.ord

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 142713

This is how I understood the question:

SQL> with school (student_name, class, sport, club) as
  2    (select 'Scott', 'x', 'a', 'c' from dual union all
  3     select 'Mike' , 'b', 'c', 'z' from dual union all
  4     select 'Kate' , 'x', 'y', 'z' from dual union all
  5     select 'John' , 'x', 'b', 'd' from dual union all
  6     select 'Vito' , 'd', 'e', 'g' from dual
  7    )
  8  select * from school
  9  where class = 'x' or sport = 'y' or club = 'z'
 10  order by case when class = 'x' then 1 end,
 11           case when sport = 'y' then 2 end,
 12           case when club  = 'z' then 3 end;

STUDENT CLASS SPORT CLUB
------- ----- ----- -----
Kate    x     y     z
Scott   x     a     c
John    x     b     d
Mike    b     c     z

SQL>

If that's not it, please, post sample data and expected result.

Upvotes: 1

Related Questions