Select Distinct Keyword Problems...

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?

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?
>

thanks for the response. the "top most" entry refers to the first entry
for each customer in the table.

based on the informaiton provided, i would have to use the first
suggestion.  However, does the min() funtction work with non numeric
values?  my picture ID's are text.

wirelessguy (law_40@hotmail.com)  writes:
> based on the informaiton provided, i would have to use the first
> suggestion.  However, does the min() funtction work with non numeric
> values?  my picture ID's are text.

Yes, MIN() works with varchar values. (I assume you don't mean the
data type text, because that would be a funny thing to use for a file
name.)


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

There is no concept in SQL Server for top entry or first entry.  Only
explict ordering based on the data.  For first entry you wound need an
additional field describing the order in which the entries were made.

"wirelessguy" <law_40@hotmail.com> wrote in message
news:1125034364.621426.10840@g47g2000cwa.googlegroups.com...
> thanks for the response. the "top most" entry refers to the first entry
> for each customer in the table.
>
> based on the informaiton provided, i would have to use the first
> suggestion.  However, does the min() funtction work with non numeric
> values?  my picture ID's are text.
>

thanks for the responses.  I looks like i will have to store an
additional piece of information in order to retreive the info that i
need.

thanks for your responses.

d

I found a solution in an old forum.  here is a simplified version of
what i end up using....

SELECT Customername, CustomerID, min(PictureID) as PictureID FROM
coupons
GROUP BY CustomerID, Customername

i just realized that this is very similir to what Steve reported
earlier. I'm not sure why that didn't work before for me.  but it works
now.

thanks for all of you help!