SQL distinct with group by in Oracle

Question

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?

Solution

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

Comments

[pathum83]

yes.i realize that error that i did.when add distinct key word to all selected columns it works.Thanks for all.

[eaolson]

If this the correct answer to your question, it would be polite to mark it as such.