Posted by Steve Kass on 08/26/05 07:02

wirelessguy,

I don't know what you mean by "topmost", but you could
pull the first in alphabetical order:

select customerID, customername, min(PictureID)
from T
group by customerID, customername

This assumes that there is a 1-1 relationship
between customerID and customername.

In fact, you should put a unique constraint on
(customerID, customername) and store that information
separately. If you need to keep all these picture file
names, store them in a separate table, with customerID
as a foreign key.

If you have more columns, or if customername can vary
per customerID, use this:

select customerID, customername, PictureID
from T
where PictureID = (
select min(PictureID)
from T as Tcopy
where Tcopy.customerID = T.customerID
)

Steve Kass
Drew University


wirelessguy wrote:
> Let's say i have a database with the following structure and data
>
> tablename: customers
>
> customerID| customername | PictureID|
>
> 1 | MyCustomer | 1.jpg |
> 1 | MyCustomer | 1_1.jpg |
> 1 | MyCustomer | 1_3.jpg |
> 2 | MyCustomer2 | 2.jpg |
> 3 | MyCustomer3 | 3.jpg |
> 3 | MyCustomer3 | 3_2.jpg |
> 4 | MyCustomer4 | 4_2.jpg |
> 4 | MyCustomer4 | 4_1.jpg |
>
> Is it possible to pull back only one entry per customer? I don't care
> which Picture ID it uses. I would perfer if the query would return the
> topmost PictureID for a customer, but i don't really care.
>
> desired output
>
> customerID| customername | PictureID|
>
> 1 | MyCustomer | 1.jpg |
> 2 | MyCustomer2 | 2.jpg |
> 3 | MyCustomer3 | 3.jpg |
> 4 | MyCustomer4 | 4_2.jpg |
>
>
> I have tried using the DISTINCT keyword, but it does not really help
> me. my original thought was to use...
>
> "Select Distinct CustomerID, Customername from Customers" but then i
> don't have access to the PictureID? can i use a sub query?
>

[Back to original message]