?
Solved

SQL CODE

Posted on 2007-10-05
7
Medium Priority
?
214 Views
Last Modified: 2010-03-19
I have a table that I need to return all columns but ONLY the ones with a DISTINCT value in one of the columns.

This code retrns 340 rows but 12 are duplicates ( product_desc)
select bev_type,franchisor,super_brand,brand,product_code,product_desc,super_package,package,
eq_eaches,eq_converted,eq_twelve_oz,eq_fountain,eq_sellunit from dbo.product
where product_desc like 'BIBPST%'
order by product_desc

how would I change it to ONLY show me the ones with distinct prduct_desc?
0
Comment
Question by:craigs052998
  • 3
  • 3
7 Comments
 
LVL 16

Expert Comment

by:SQL_SERVER_DBA
ID: 20023025
select DISTINCT product_desc, bev_type,franchisor,super_brand,brand,product_code,super_package,package,
eq_eaches,eq_converted,eq_twelve_oz,eq_fountain,eq_sellunit from dbo.product
where product_desc like 'BIBPST%'
order by product_desc
0
 

Author Comment

by:craigs052998
ID: 20023041
I forgot to mention there are some columns with differnt items because when the data was loaded they did not check for that product already.  The issues I have is 1 product description with 3 product codes or some other value of some column
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 20023179
select bev_type,franchisor,super_brand,brand,product_code,MIN(product_desc) Product_Desc,super_package,package,
eq_eaches,eq_converted,eq_twelve_oz,eq_fountain,eq_sellunit
 from dbo.product
where product_desc like 'BIBPST%'
GROUP BY bev_type,franchisor,super_brand,brand,product_code,super_package,package,eq_eaches,eq_converted,eq_twelve_oz,eq_fountain,eq_sellunit
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:craigs052998
ID: 20023233
anee  still gives me 340 rows not the 328 I am looking for
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 20023811
can you just paste some duplicate entries here along with the expected results
0
 

Author Comment

by:craigs052998
ID: 20023908
CSD      BIBPST CADBURY                 BIBPST DR PEPPER               BIBPST DR PEPPER               4120      BIBPST 5 GAL DR PEPPER                        FOUNTAIN PST/BIB               BIB/PST 5                      13.559716      5      13.3333      5      5

CSD      BIBPST CADBURY                 BIBPST DR PEPPER               BIBPST DR PEPPER               1336      BIBPST 5 GAL DR PEPPER                        FOUNTAIN PST/BIB               BIB/PST 5                      13.559716      5      13.3333      5      5

I only want to see one of them.  The product code is the only non dupe in this one   1336 and 4120


0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 800 total points
ID: 20023945
select bev_type,franchisor,super_brand,brand,MAX(product_code)product_code,  Product_Desc,super_package,package,
eq_eaches,eq_converted,eq_twelve_oz,eq_fountain,eq_sellunit
 from dbo.product
where product_desc like 'BIBPST%'
GROUP BY bev_type,franchisor,super_brand,brand,Product_Desc,super_package,package,eq_eaches,eq_converted,eq_twelve_oz,eq_fountain,eq_sellunit
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are some very powerful Dynamic Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a di…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question