CODEWITHSUNDEEP
mysqlsqloracle-database
user2553189
user2553189

Reputation: 51

Order the columns returned by a query

I have this query

SELECT DISTINCT RE1.NAME AS NAME1,RE2.NAME AS NAME2 
FROM RATING R1,RATING R2,REVIEWER RE1,REVIEWER RE2
WHERE R1.RID > R2.RID 
  AND R1.MID  = R2.MID 
  AND RE1.RID = R1.RID 
  AND RE2.RID = R2.RID;

The output is

NAME1     NAME2

CHRIS     BRITTANY
ASHLEY    CHRIS
JAMES     ELIZABETH

I want to return the table in such a way that the rows are in alphabetical order, i.e. the output should be:

NAME1     NAME2

BRITTANY   CHRIS
ASHLEY     CHRIS
ELIZABETH  JAMES

Upvotes: 2

Views: 134

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270001

You can use the least() and greatest() functions:

SELECT DISTINCT least(RE1.NAME, re2.name) AS NAME1, greatest(re1.name, RE2.NAME) AS NAME2 
FROM RATING R1,RATING R2,REVIEWER RE1,REVIEWER RE2
WHERE R1.RID > R2.RID 
  AND R1.MID  = R2.MID 
  AND RE1.RID = R1.RID 
  AND RE2.RID = R2.RID;

You should also learn proper join syntax:

SELECT DISTINCT least(RE1.NAME, re2.name) AS NAME1, greatest(re1.name, RE2.NAME) AS NAME2 
from rating r1 join
     rating r2
     on R1.RID > R2.RID AND R1.MID  = R2.MID and
     reviewer re1
     on RE1.RID = R1.RID join
     reviewer re2
     on RE2.RID = R2.RID  ;

EDIT:

If your database doesn't support these two functions, you can easily do the same things with a case statement:

select distinct (case when RE1.NAME < re2.name then re1.name else re2.name end) AS NAME1,
       (case when RE1.NAME < re2.name then re2.name else re1.name end) as NAME2
. . .

As for the issue with NULLs. The original data has the names populated and the joins are all inner joins, so NULL did not seem to be an issue.

Upvotes: 10

Related Questions