5

I am using the following code for getting value from the database:

But when i wrote this code (testing as to see where the problem is..) i noticed the query is not fetching the distinct value out from the database: Here is the query

select distinct(ca.id)as id, acc.name as accName,pIsu.name as pareentIsu, sIsu.name as subIsu , dv.domain_value as contractType,acc.id as accId,dvct.domain_value as contractstatus
from contracts_account ca left join business_unit pIsu on ca.parent_isu_fk = pIsu.id
left join business_unit sIsu on ca.sub_isu_fk = sIsu.id
left join business_unit acc on ca.account_fk = acc.id
left join contracts con on con.contracts_account_fk = ca.id
left join domain_values dv on dv.id = con.contract_type_fk
left join domain_values dvct on dvct.id = con.contract_status_fk
where ca.id is not null and con.contract_type_fk in ( 4466079 ) order by ca.id

This query is simply not returning me 'Distinct Id'
What am i doing wrong? I am using postgres 8.2

carexcer
  • 1,407
  • 2
  • 15
  • 27
SeasonalShot
  • 2,357
  • 3
  • 31
  • 49
  • `Select distinct col1, col2, col3 ...` will not return just distinct col1 values, it will return distinct col1 + col2 + col3 combinations – Dan Jan 16 '14 at 15:50

2 Answers2

14

Your query is interpreted as:

select distinct (ca.id) as id, acc.name as accName, . . .

This is a standard distinct statement. If you want just one row per ca.id with values from the first row (based on the order by), then use distinct on:

select distinct on (ca.id) ca.id, acc.name as accName, . . .
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
2

Are you perhaps trying to use PostgreSQL's DISTINCT ON syntax?

http://www.postgresql.org/docs/current/static/sql-select.html#SQL-DISTINCT

ie. missing the ON?

David Aldridge
  • 51,479
  • 8
  • 68
  • 96