CODEWITHSUNDEEP
sqlpostgresql
Ram Bodke
Ram Bodke

Reputation: 7

i am not getting distinct record using this query

 select distinct
 (mc."Member Id"),
mc."Member Last Name",
mc."Member Middle Name",
mc."Member First Name",

mc."Member DOB",
mc."Claim DOS",
mwc."Later Visit Date",
mc."Service Paid/Denied",
mc."Claim Number",
mc."Facility Name",
mc."Facility Id",
mc."Facility Address",
mc."Facility Phone Number",
mc."Facility Fax",

mc."Provider Name",
mc."Provider Id"
from member_cdt mc  inner join member_withoutcdt mwc ON mc."Member Id" = mwc."Member Id" 
where mc."Claim DOS" <>  mwc."Later Visit Date"
order by 1

Upvotes: 1

Views: 38

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270091

You are looking for distinct on, not select distinct:

select distinct on (mc."Member Id") mc."Member Id", . . .
from member_cdt mc inner join
     member_withoutcdt mwc
     on mc."Member Id" = mwc."Member Id" 
where mc."Claim DOS" <>  mwc."Later Visit Date"
order by mc."Member Id";

This syntax (which is specific to Postgres), returns one row for each set of key values in parentheses. The contents of the row are the columns after the parentheses.

Notes:

  • There is no comma after the closing paren for distinct on.
  • mc."Member Id" is both in parentheses and in the column list afterwards. The first reference is used for selecting unique rows; the second is for returning the value.
  • Normally, there is an additional order by key to specify which of the duplicate records that you want.

Upvotes: 2

Related Questions