CODEWITHSUNDEEP
sqloracle-databasegroup-bydistinct
pathum83
pathum83

Reputation: 133

SQL distinct with group by in Oracle

I have following SQL:

select 
  origin,destination,to_char(to_date(substr(ship_date,1,6),'YYMMDD'),
  'YYYY-MM-DD'),ship_date,trip_number, distinct ship_number  
from shipment a 
where 
  a.scc_code in ('xxxxx','xxxxx','xxxxx') 
  and load_status = 'S' and ship_date like '11%' 
  and shipper_id = XXXXXX
group by origin,destination,ship_date,trip_number, ship_number

When I run this SQL in Oracle it gives ORA-00936: missing expression. If I remove the distinct keyword, it runs fine. Can anybody tell me the difference between those two things?

Upvotes: 7

Views: 44003

Answers (1)

narek.gevorgyan
narek.gevorgyan

Reputation: 4185

Distinct keyword is for all selected columns, so you have to put it before select

select distinct 
  origin,destination,to_char(to_date(substr(ship_date,1,6),'YYMMDD'),
  'YYYY-MM-DD'),ship_date,trip_number, ship_number  
from shipment a 
where 
  a.scc_code in ('xxxxx','xxxxx','xxxxx') 
  and load_status = 'S' and ship_date like '11%' 
  and shipper_id = XXXXXX
group by origin,destination,ship_date,trip_number, ship_number

Upvotes: 15

Related Questions