Re: Sort of Complex Query - Howto Eliminate Repeating Results

On Thu, Jan 12, 2006 at 09:08:59AM -0800, operationsengineer1(at)yahoo(dot)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.

> 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