sql - Postgresql Retrieving distinct row

Question

In the table below.. I am supposed to retrieve all row where the deleted is false and disabled is true and a distinct phrase.. If the phrase isn't the only one in the table (for example the "bad" word).. I must return the one with the device_id.. If it is only one in the table, I must return it even if the device_id is blank..

 id | device_id | phrase  | disabled | deleted | 
----+-----------+---------+----------+---------+
  2 |         1 | WTF     | f        | f       |
  3 |         1 | White   | f        | f       |
  4 |           | WTF     | f        | f       |
  5 |           | wTf     | f        | f       |
  6 |         2 | fck     | f        | f       |
  7 |         1 | damn    | f        | f       |
  8 |         1 | bitch   | f        | f       |
  9 |         1 | crap    | f        | f       |
  1 |         1 | Shit    | t        | t       |
 10 |         1 | ass     | f        | f       |
 11 |           | bad     | f        | f       |
 12 |         1 | bad     | t        | f       |
 13 |         1 | badshit | f        | f       |

What I've done is this query and returns what I've expected.. (for example, the return is only 1 "bad" word with device_id = 1)

select distinct on (phrase) id, device_id, phrase, disabled, deleted 
from filter
where phrase like '%' and deleted = false and
     (device_id is null or device_id = 1)
order by phrase;

But when add a keyword search for example the "bad"..

select distinct on (phrase) id, device_id, phrase, disabled, deleted 
from filter
where phrase like '%bad%' and deleted = false and
      (device_id is null or device_id = 1)
order by phrase;

The return is "badshit" (ok) and "bad" (but the device_id is null).. My expected is that the "bad" word's device_id is 1..

I'm kind of new to postgresql.. Thanks!

Answer 1

DISTINCT ON ( expression [, ...] ) keeps only the first row of each set of rows where the given expressions evaluate to equal. The DISTINCT ON expressions are interpreted using the same rules as for ORDER BY (see above). Note that the "first row" of each set is unpredictable unless ORDER BY is used to ensure that the desired row appears first. For example:

SELECT DISTINCT ON (location) location, time, report
    FROM weather_reports
    ORDER BY location, time DESC;

retrieves the most recent weather report for each location. But if we had not used ORDER BY to force descending order of time values for each location, we'd have gotten a report from an unpredictable time for each location.

The DISTINCT ON expression(s) must match the leftmost ORDER BY expression(s). The ORDER BY clause will normally contain additional expression(s) that determine the desired precedence of rows within each DISTINCT ON group

for your case use below code as you want device_id=1

select distinct on (phrase) phrase, id, device_id, disabled, deleted 
from filter
where phrase like '%bad%' and deleted = false and
       device_id = 1
order by phrase,device_id;

Answer 2

I already fixed this error 9 months ago but was too busy to post it here.

Here's my answer:

order by phrase, device_id

Answer 3

Nevermind, I fixed it by adding device_id:

order by phrase;

into

order by phrase, device_id;

Answer 4

either:

select distinct on (phrase) id, device_id, phrase, disabled, deleted 
from filter
where phrase like '%bad%' and deleted = false and
  (device_id is not null)
order by phrase;

or:

select distinct on (phrase) id, device_id, phrase, disabled, deleted 
from filter
where phrase = 'bad' and deleted = false and
  (device_id is null or device_id = 1)
order by phrase;

first if you want to only retrieve records without null values in device. second if you want to retrieve records with exact phrase bad.

where phrase like '%bad%'

specifically asks postgres to return both bad and bad****, because they are both 'like' bad.

On another note, clean up your post before asking for help.