CODEWITHSUNDEEP
sqlpostgresqlsql-order-by
itdxer
itdxer

Reputation: 1256

Postgres order row for exist field

I have table users and in this table I have column avatar which save path to user avatar. How I can order my query which starts from user which have avatar and after them which heven't. Example:

Table:

 id|avatar|
___|______|
 1 |  p1  |
 2 |      |
 3 |      |
 4 |  p4  |

Result: 

 id|avatar|
___|______|
 1 |  p1  |
 4 |  p4  |
 2 |      |
 3 |      |

Upvotes: 0

Views: 582

Answers (1)

mu is too short
mu is too short

Reputation: 434975

Assuming that those blanks for 2 and 3 are NULLS then ORDER BY already does what you want; ORDER BY puts NULLS last by default when you sort ascending:

If NULLS LAST is specified, null values sort after all non-null values; if NULLS FIRST is specified, null values sort before all non-null values. If neither is specified, the default behavior is NULLS LAST when ASC is specified or implied, and NULLS FIRST when DESC is specified (thus, the default is to act as though nulls are larger than non-nulls).

So something simple like:

order by avatar

would probably do the trick. If you want to be explicit about it, then:

order by avatar nulls last

or

order by avatar asc nulls last

Upvotes: 2

Related Questions