Home order the results of one table based on it's availability in another table psql
Reply: 3

order the results of one table based on it's availability in another table psql

Gaurav
1#
Gaurav Published in 2018-02-14 08:13:33Z

I have 5 tables A, A1, A2, A3, A4. A has the foreign key relationship with remaining 4 tables.

I want order A by first checking availability in A1 first then in A2 and so on.

So results should be displayed in order by it's availability in A1, A2, A3, A4.

select * from A as main
order by (select abc from A1 where main.abc = A1.abc), (select abc from A2 where main.abc = A2.abc), (select abc from A3 where main.abc = A3.abc), (select abc from A4 where main.abc = A4.abc)

if there are 10 records in A table then i want all 10 records in result but that records should be ordered by it's availability in A1, A2, A3 and A4 little help will be appreciated.

Radim Bača
2#
Radim Bača Reply to 2018-02-14 11:39:18Z

Use LEFT JOIN and COALESCE

select distinct A.* 
from A
LEFT JOIN A1 ON A.abc = A1.abc
LEFT JOIN A2 ON A.abc = A2.abc
LEFT JOIN A3 ON A.abc = A3.abc
LEFT JOIN A4 ON A.abc = A4.abc
ORDER BY COALESCE(A1.abc,A2.abc,A3.abc,A4.abc)

However, it does not make much sense. The ordering will be according to the abc value. If you intend to order according to some value in A1 - A4, then use it instead of abc in COALESCE.

If you want rows being in A1 first then you might employ || to enforce ordering according to A1 - A4 existence (if the abc attribute is varchar)

select distinct A.* 
from A
LEFT JOIN A1 ON A.abc = A1.abc
LEFT JOIN A2 ON A.abc = A2.abc
LEFT JOIN A3 ON A.abc = A3.abc
LEFT JOIN A4 ON A.abc = A4.abc
ORDER BY COALESCE('a' || A1.abc, 'b' || A2.abc, 'c' || A3.abc, 'd' || A4.abc)
Vignesh Kumar
3#
Vignesh Kumar Reply to 2018-02-15 05:23:30Z

Use COALESCE

SELECT A.* 
FROM A LEFT JOIN A1 ON A.ID = A1.ID
LEFT JOIN A2 ON A.ID = A2.ID 
LEFT JOIN A3 ON A.ID = A3.ID 
LEFT JOIN A4 ON A.ID = A4.ID 
ORDER BY COALESCE(A1.ID ,A2.ID ,A3.ID ,A4.ID )
joop
4#
joop Reply to 2018-02-15 16:01:41Z

You can sort on EXISTance:


-- sample data
CREATE TABLE a ( abc integer not null primary key);
INSERT INTO a(abc)
select generate_series(1,25);

CREATE TABLE a2 AS select * FROM a where abc %2 = 0;
CREATE TABLE a3 AS select * FROM a where abc %3 = 0;
CREATE TABLE a5 AS select * FROM a where abc %5 = 0;
CREATE TABLE a7 AS select * FROM a where abc %7 = 0;

ALTER TABLE a2 ADD primary key(abc);
ALTER TABLE a3 ADD primary key(abc);
ALTER TABLE a5 ADD primary key(abc);
ALTER TABLE a7 ADD primary key(abc);

-- Query


select * from a
ORDER BY exists (select * from a2 where abc=a.abc) desc
        , exists (select * from a3 where abc=a.abc) desc
        , exists (select * from a5 where abc=a.abc) desc
        , exists (select * from a7 where abc=a.abc) desc
        , a.abc -- tie-breaker
     ;

Result:


CREATE TABLE
INSERT 0 25
SELECT 12
SELECT 8
SELECT 5
SELECT 3
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
 abc 
-----
   6
  12
  18
  24
  10
  20
  14
   2
   4
   8
  16
  22
  15
  21
   3
   9
   5
  25
   7
   1
  11
  13
  17
  19
  23
(25 rows)
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.316075 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO