CODEWITHSUNDEEP
sqlpostgresql
sonalkr132
sonalkr132

Reputation: 1037

pg: left join on table puts nil records first

Here is my project table:

 Column   |            Type             |                       Modifiers                       | Storage  | Stats target | Description 
------------+-----------------------------+-------------------------------------------------------+----------+--------------+-------------
 id         | integer                     | not null default nextval('projects_id_seq'::regclass) | plain    |              | 
 name       | character varying(255)      |                                                       | extended |              | 
 repo_id    | integer                     |                                                       | plain    |              | 
 created_at | timestamp without time zone |                                                       | plain    |              | 
 updated_at | timestamp without time zone |                                                       | plain    |              | 
 user_id    | integer                     |                                                       | plain    |              | 
 data_path  | character varying(255)      |                                                       | extended |              | 
 private    | boolean                     | default false                                         | plain    |              | 
 uniqueurl  | character varying(255)      |                                                       | extended |              | 
 urlbase    | character varying(255)      |                                                       | extended |              | 
 ancestry   | character varying(255)      |                                                       | extended |              | 
 deleted_at | timestamp without time zone |                                                       | plain    |              | 
Indexes:
    "projects_pkey" PRIMARY KEY, btree (id)
    "index_projects_on_name_and_user_id" UNIQUE, btree (name, user_id)
    "index_projects_on_ancestry" btree (ancestry)
    "index_projects_on_deleted_at" btree (deleted_at)
Has OIDs: no

and here is my rating_cache table:

 Column     |            Type             |                         Modifiers                          | Storage  | Stats target | Description 
----------------+-----------------------------+------------------------------------------------------------+----------+--------------+-------------
 id             | integer                     | not null default nextval('rating_caches_id_seq'::regclass) | plain    |              | 
 cacheable_id   | integer                     |                                                            | plain    |              | 
 cacheable_type | character varying(255)      |                                                            | extended |              | 
 avg            | double precision            | not null                                                   | plain    |              | 
 qty            | integer                     | not null                                                   | plain    |              | 
 dimension      | character varying(255)      |                                                            | extended |              | 
 created_at     | timestamp without time zone |                                                            | plain    |              | 
 updated_at     | timestamp without time zone |                                                            | plain    |              | 
Indexes:
    "rating_caches_pkey" PRIMARY KEY, btree (id)
    "index_rating_caches_on_cacheable_id_and_cacheable_type" btree (cacheable_id, cacheable_type)
Has OIDs: no

I am doing left outer join on two tables with:

SELECT  "projects".* FROM "projects" LEFT OUTER JOIN rating_caches
ON rating_caches.cacheable_id = projects.id 
WHERE "projects"."deleted_at" IS NULL  ORDER BY rating_caches.avg desc

this orders the projects correctly (highest avg come first) however projects with no matching record in rating_caches table come even before the highest avg. For ex: projects:

id  name
1   A
2   B
3   C

rating_caches:

id cacheable_id avg
1  3            3.0
2  2            2.5

result of query looks like:

id name
1  A
3  C
2  B

Shouldn't the project with id = 1 come last?

Upvotes: 0

Views: 42

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Just use NULLS LAST:

ORDER BY rating_caches.avg desc NULLS LAST

As a note, the documentation specifies:

The NULLS FIRST and NULLS LAST options can be used to determine whether nulls appear before or after non-null values in the sort ordering. By default, null values sort as if larger than any non-null value; that is, NULLS FIRST is the default for DESC order, and NULLS LAST otherwise.

Upvotes: 2

Related Questions