Re: Sort of Complex Query - Howto Eliminate Repeating Results - Mailing list pgsql-novice

> On Thu, Jan 12, 2006 at 09:08:59AM -0800,
> operationsengineer1@yahoo.com wrote:
> > they query i'm using is as follows:
> >
> > SELECT t_product.product_id,
> >        t_product.product_number,
> >        t_serial_number.serial_number_id,
> >        t_serial_number.serial_number,
> > FROM t_serial_number
>
> Are you sure this is the query you're using?  It has
> a syntax error
> due to the comma after the final field in the select
> list.  As
> written this query shouldn't run at all, so it's
> hard for us to
> trust that it's what you're really doing.

i deleted a 5th select result in yahoo w/o deleting
the comma - whoops!  otherwise it is 100% what i'm
using.  oh, and you are right - it will not work with
the extra comma.

> > my last problem is that serial number repeats for
> each
> > inspection.  let's say 2/n has four fails w/o a
> pass.
> > it will display four rimes.  i want it to display
> a
> > single time.  select distinct didn't work.  i
> don't
> > know if it is possible to get distinct values
> withing
> > an ON clause.
>
> How didn't SELECT DISTINCT work?  Did it return the
> wrong results?
> Did it fail with a syntax error?  If you got an
> error like
>
> ERROR:  for SELECT DISTINCT, ORDER BY expressions
> must appear in select list
>
> then try qualifying serial_number in the ORDER BY
> clause, like this:
>
> ORDER BY t_serial_number.serial_number::int ASC;
>
> --
> Michael Fuhr

Michael, i did as you said.  i've posted the select
statement (exactly, this time!) and the resulting
error.

select statement:

SELECT DISTINCT t_product.product_id,
       t_product.product_number,
       t_serial_number.serial_number_id,
       t_serial_number.serial_number
FROM t_serial_number
LEFT JOIN t_link_contract_number_job_number
    ON (
t_serial_number.link_contract_number_job_number_id =

t_link_contract_number_job_number.link_contract_number_job_number_id
       )
LEFT JOIN t_job_number
   ON (
t_link_contract_number_job_number.job_number_id =
        t_job_number.job_number_id
      )
LEFT JOIN t_product
   ON ( t_product.product_id =
        t_job_number.product_id
      )
LEFT JOIN t_inspect
   ON ( t_serial_number.serial_number_id =
        t_inspect.serial_number_id
      )
LEFT JOIN t_inspect_area
   ON ( t_inspect.inspect_area_id =
        t_inspect_area.inspect_area_id
      )
WHERE t_serial_number.serial_number_id NOT IN
      (SELECT serial_number_id FROM t_inspect
       WHERE t_inspect_area.inspect_area_id = 1
       AND inspect_pass = true)
       OR t_inspect_area.inspect_area_id IS NULL
ORDER BY t_serial_number.serial_number::int ASC

resulting error:

ERROR:  for SELECT DISTINCT, ORDER BY expressions must
appear in select list

(good call onthe type of error).

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com