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!

John Christopher Azcarraga · Accepted Answer · 2018-08-08 06:27:50Z

1

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

answered Aug 8, 2018 at 6:27

John Christopher Azcarraga

111 bronze badge

Sign up to request clarification or add additional context in comments.

Comments

John R · Accepted Answer · 2017-10-25 03:12:51Z

0

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.

answered Oct 25, 2017 at 3:12

John R

1,51611 silver badges19 bronze badges

1 Comment

ctrlnot Over a year ago

I tried your second one and it returns the "bad" with no device_id.. What I want is to retrieve the one with device_id since it has a duplicate "bad" with device_id (which is 1).. If there's no duplicate, retrieve it even it has no device_id.. Also what do you mean by cleaning up my post?

ctrlnot · Accepted Answer · 2017-10-25 06:25:18Z

0

Nevermind, I fixed it by adding device_id:

order by phrase;

into

order by phrase, device_id;

answered Oct 25, 2017 at 6:25

ctrlnot

32 silver badges4 bronze badges

Comments

Zaynul Abadin Tuhin · Accepted Answer · 2018-08-08 07:29:07Z

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;

Collectives™ on Stack Overflow

sql - Postgresql Retrieving distinct row

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related