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!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|||>> 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.|||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 <srielau@.ca.eye-be-em.com> wrote in message news:<cbhbe4$f3q$1@.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|||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 <srielau@.ca.eye-be-em.com> wrote in message news:<cbhbe4$f3q$1@.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
No comments:
Post a Comment