Skip site navigation (1) Skip section navigation (2)

Re: SQL Question - Almost, Not Quite...

From: <operationsengineer1(at)yahoo(dot)com>
To: Bruno Wolff III <bruno(at)wolff(dot)to>
Cc: "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org>
Subject: Re: SQL Question - Almost, Not Quite...
Date: 2006-05-25 20:56:09
Message-ID: 20060525205609.61883.qmail@web33313.mail.mud.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-novice
> On Wed, May 24, 2006 at 15:06:53 -0700,
>   operationsengineer1(at)yahoo(dot)com wrote:
> > 
> > given this structure, and hopefully i've
> communicated
> > it well enough, i'm concerned that it might be
> > difficult to weed out qa 1 fail followed by qa 2
> pass.
> > 
> > that is...
> > 
> > qa test 1
> > fail
> > 
> > ... the bizrre happens ...
> > 
> > qa test 2
> > pass
> > 
> > how can i go about finding the qa test 1 failure? 
> > 
> > if i did a inspect_date descending query with a
> limit
> > of 1, i'd get a pass and miss the failure.
> 
> Use "inspect_pass" in the WHERE clause.

Bruno, i don't understand how this would help my
situation, but i think the problem is that i poorly
communicated the original problem.  ;-)

however, i am very close to what i want...  but not
quite there.

i use this query...

SELECT t_inspect_result.inspect_result,
t_inspect.inspect_id
FROM t_inspect_result, t_inspect, t_inspect_area,
     t_serial_number,
t_link_contract_number_job_number, t_job_number,
t_product
WHERE  t_inspect.inspect_area_id =
       t_inspect_area.inspect_area_id
AND t_inspect.serial_number_id =
    t_serial_number.serial_number_id
AND t_serial_number.link_contract_number_job_number_id
=
   
t_link_contract_number_job_number.link_contract_number_job_number_id
AND t_link_contract_number_job_number.job_number_id =
    t_job_number.job_number_id
AND t_product.product_id =
    t_job_number.product_id
AND t_inspect.inspect_id =
    t_inspect_result.inspect_id
AND t_inspect.serial_number_id = '184'
GROUP BY t_inspect_result.inspect_timestamp,
t_inspect_result.inspect_result, t_inspect.inspect_id
ORDER BY t_inspect_result.inspect_timestamp desc

to get these results...

Row / inspect_result / inspect_id
1 / t / 107
2 / t / 106
3 / f / 100
4 / t / 100

i can't figure out how to group by
t_inspect.inspect_id and limit the query to one result
per inspect_id.

iow, i want to see...

Row / inspect_result / inspect_id
1 / t / 107
2 / t / 106
3 / f / 100

when i apply a limit 1, it returns a single result,
not a single result per inspect_id.  i tried (), but
to no avail.

any guidance is, as always, much appreciated.

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

In response to

Responses

pgsql-novice by date

Next:From: Bruno Wolff IIIDate: 2006-05-25 21:17:54
Subject: Re: SQL Question - Almost, Not Quite...
Previous:From: Bruno Wolff IIIDate: 2006-05-25 03:30:40
Subject: Re: SQL Question

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group