Beginner's Question on SQL-DISTINCT

[kackson]

Hi.

I did this
SELECT BOTTLEA, BOTTLEB, BOTTLEC FROM MYPRODUCTS

But, I would like to have the result returned only for distinct
BOTTLEB. BOTTLEA and C can be anything. But the result that I get from
the statement must have BOTTLEB as distinct. How do I do that?

I tried

SELECT DISTINCT BOTTLEA, BOTTLEB, BOTTLEC FROM MYPRODUCTS

The result returned include duplicated BOTTLEB. I'm stuck. Appreciate
any help. Thanks in advance!

[Serge Rielau]

SELECT MAX(BOTTLEA), BOTTLEB, MAX(BOTTLEC) FROM MYPRODUCTS
GROUP BY BOTTLEB

(MAX being a subset of "anything" ;-)
If SQL Server allows TOP in subqueries you could also do a correlated join:
SELECT ANY.BOTTLEA, DIST.BOTTLEB, ANY.BOTTLEC
FROM (SELECT DISTINCT BOTTLEB FROM MYPRODUCTS) AS DIST,
(SELECT TOP 1 BOTTLEA, BOTTLEB, BOTTLEC FROM MYPRODUCTS
WHERE BOTTLEB = DIST.BOTTLEB) AS ANY

(hope I got the TOP syntax right)

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[--CELKO--]

>> I tried > SELECT DISTINCT BOTTLEA, BOTTLEB, BOTTLEC FROM
MYPRODUCTS; The result returned include duplicated BOTTLEB. <<

The SELECT DISTINCT clause works on whole rows and removes redundant
duplicates; it has nothing to do with columns.

Do a GROUP BY on Bootle_B and some aggregatge function on the other
two.

[kackson]

Thanks for the pointer. It works!

But if I want to include a requirement that says where BOTTLEB >= 20
dollars, it doesnt seem to work.

Doesnt work->
SELECT MAX(BOTTLEA), BOTTLEB, MAX(BOTTLEC) FROM MYPRODUCTS GROUP BY
BOTTLEB WHERE BOTTLEB>= 20

Any advise please? Thank you once again!

Serge Rielau <sr*****@ca.eye-be-em.com> wrote in message news:<cb**********@hanover.torolab.ibm.com>...

SELECT MAX(BOTTLEA), BOTTLEB, MAX(BOTTLEC) FROM MYPRODUCTS
GROUP BY BOTTLEB

(MAX being a subset of "anything" ;-)
If SQL Server allows TOP in subqueries you could also do a correlated join:
SELECT ANY.BOTTLEA, DIST.BOTTLEB, ANY.BOTTLEC
FROM (SELECT DISTINCT BOTTLEB FROM MYPRODUCTS) AS DIST,
(SELECT TOP 1 BOTTLEA, BOTTLEB, BOTTLEC FROM MYPRODUCTS
WHERE BOTTLEB = DIST.BOTTLEB) AS ANY

(hope I got the TOP syntax right)

Cheers
Serge

[kackson]

Never mind about my last message.
I just have to reverse the order and it works again.

SELECT MAX(BOTTLEA), BOTTLEB, MAX(BOTTLEC) FROM MYPRODUCTS WHERE
BOTTLEB>= 20 GROUP BY BOTTLEB

Thanks!!!

Serge Rielau <sr*****@ca.eye-be-em.com> wrote in message news:<cb**********@hanover.torolab.ibm.com>...

SELECT MAX(BOTTLEA), BOTTLEB, MAX(BOTTLEC) FROM MYPRODUCTS
GROUP BY BOTTLEB

(MAX being a subset of "anything" ;-)
If SQL Server allows TOP in subqueries you could also do a correlated join:
SELECT ANY.BOTTLEA, DIST.BOTTLEB, ANY.BOTTLEC
FROM (SELECT DISTINCT BOTTLEB FROM MYPRODUCTS) AS DIST,
(SELECT TOP 1 BOTTLEA, BOTTLEB, BOTTLEC FROM MYPRODUCTS
WHERE BOTTLEB = DIST.BOTTLEB) AS ANY

(hope I got the TOP syntax right)

Cheers
Serge