Re: Distinct rows

Chris,
  If I'm understanding your request correctly you want to return only one row per vendor. This SQL will accomplish that

select distinct(a.vendor_id),

         max(b.product),
         max(c.description)
from     vendors a,
         vendors b,
         vendors c
where    b.vendor_id = a.vendor_id
 and     c.vendor_id = a.vendor_id
 and     c.product = b.product

Note that this way, you only will see one product per vendor and the description of that product. The SQL accounts for the possiblility of multiple products per vendor and multiple descriptions per product per vendor, but it can't "guess" which particular product and description is the most desirable to display.

In article <ur0Q3.4775$Qo3.98872_at_wbnws01.ne.mediaone.net>,   "Chris Jordan" <mail_at_mailer.com> wrote:
> well, this doesn't work either. Because 'distinct' refers to entire
 record.
> I want distict vendors. So from the example
> below, only two records get returned instead of three as there are
 only 2
> distinct VENDORS even though there are
> 3 distinct RECORDS.
> Peter Okhiria <peter.okhiria_at_mci.com> wrote in message
> news:38103931.A35CDAE2_at_mci.com...
> > use
> > select distinct vendorid, product, description from vendors where
> > vendorid < 100
> >
> > Chris Jordan wrote:
> >
> > > Hi I have a query (select vendorid, product, description from
 vendors
 where
> > > vendorid < 100) that is returning duplicate information, ie.
> > > VENDORID PRODUCT DESCRIPTION
> > > 1 Switches RKS
> > > 1 Switches DDK
> > > 2 UPS New
> > >
> > > What query can I use that displays only one instance of a specifc
 vendor,
> > > i.e in the above, only vendorids 1 & 2 are
> > > displayed for a total of two records(1,switches,RKS) and
 (2,UPS,New).
> > >
> > > Thanks,
> > > Chris
> >
> >
> >

>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sat Oct 23 1999 - 00:00:00 CEST