|Subject||Re: How to display only the first instance of a value|
|Author||Svein Erling Tysvær|
--- In email@example.com, "Terry V." wrote:
> What I am attempting to do is to ascertain which suppliers in a
> table have overdue items on PO's. The way I am looking to do this is
> to pull the overdue items and filter the result so that only the
> first overdue item is shown for each supplier at which point I'll
> pull the supplier values into a string list thus giving a list of
> suppliers with overdue items. I am unsure of how to attain this
> result and would appreciate any assistance.
you can get the list of suppliers several ways. Either
select distinct supplier
where overdue = 'True'
or (this can also show overdue item, something the above syntax
select s1.supplier, s1.overdueitem
from suppliertable s1
where s1.overdue = 'True'
and not exists(select *
from suppliertable s2
where s2.supplier = s1.supplier
and s2.overdue = 'True'
and s2.uniquesupplynumber < s1.uniquesupplynumber)
I guess you know how to obtain the stringlist from here.