Query with strange criteria 1

[bernie10]

Hi everyone,

Just wanted to know if anyone had any thoughts on this.

Suppose I have a table as follows:

Supplier Price Valid
Jim’s 24.32 Y
Bill’s 22.11 N
Joe’s 26.01 N
Joe’s 29.04 Y
Bill’s 24.12 N
Jim’s 25.12 Y

I would like to get a record with the lowest price for each supplier. But, I only want valid records to be considered. So this is done easily enough, I simply make a query where Valid = “Y” and then make another query based off that one to find the min price grouping by manufacturer.

Here’s the problem- I have been asked to add the following piece. IF the supplier has no valid records, then I need to include his lowest priced item on the list, even though the record is marked as not being valid... this is ONLY if the supplier has no valid records. So for the above table the output should be as follows:

Supplier Price Valid
Jim’s 24.32 Y
Bill’s 22.11 N
Joe’s 29.04 Y

Notice since Bill did not have a valid record we picked one his invalid record’s with the lowest price. Can this be done? I can’t think of any way, but I thought maybe someone out there might have some ideas.

Thanks in advance for any help,
Collen

 

[steven290]

try something like

select supplier,min(price)
from tblname
group by supplier

 

[Golom]

Maybe something like

(select supplier,min(price)
from tblname
Where Valid = 'Y'
group by supplier)

UNION 

(select supplier,min(price)
from tblname
Where Valid = 'N' AND Supplier NOT IN 
(Select DISTINCT Supplier From tblname Where Valid = 'Y')
group by supplier)

 

[bernie10]

Golom,
That did it- thanks.

Collen