Database Discussion - Using SQL Distinct.. Help!

Print Page | Close Window

Using SQL Distinct.. Help!

Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: Database Discussion
Forum Description: Discussion and chat on database related topics.
URL: https://forums.webwiz.net/forum_posts.asp?TID=12078
Printed Date: 30 March 2026 at 3:03pm
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com

Topic: Using SQL Distinct.. Help!

Posted By: nkewney
Subject: Using SQL Distinct.. Help!
Date Posted: 08 October 2004 at 10:50am

Hi there,

I was hoping someone could help me with a problem I have with an SQL query.

There are some instances in the database where there are entries with the same 'item_product_code', but with different prices depending on the item_stone_field (see sample below)

What i need to do is get some SQL to return all UNIQUE item_product_code fields, and if there are more than one, just list the first. (The rest will be dealt with in another query).

item_id item_supplier item_product_code item_ cat_finish  item_cost_price item_stone
28 2  1016   Sterling   6.95   green
29 2  1016   Sterling &nb sp;6.95   black
30 2  1016   Sterling   4.95   blue
33 2 1014   Sterling   4.95   blue

This is my latest attempt which unforunately doesn't work!!

Sql = "SELECT DISTINCT(item_product_code), item_cost_price FROM Products WHERE Products.item_cat_prodtype ='" & insBrowse & "' GROUP BY item_product_code, item_cost_price "

Any suggestions?

Cheers

Nick

Replies:

Posted By: theSCIENTIST
Date Posted: 08 October 2004 at 8:14pm

SELECT TOP 1 ... should do it.

-------------
:: http://www.mylittlehost.com/ - www.mylittlehost.com

Posted By: nkewney
Date Posted: 09 October 2004 at 2:55am

Will 'top' work if i want to return the first of every similar entry in the table?

Nick

Posted By: Mart
Date Posted: 09 October 2004 at 3:59am

Not for that, no. Can you run it through query analyser and show us the output and tell us what you want it to look like?

Posted By: nkewney
Date Posted: 09 October 2004 at 12:44pm

Thanks for your help so far.

This is the query I'm using...

SELECT DISTINCT (item_product_code), item_cost_price
FROM Products
WHERE Products.item_cat_prodtype='rings'
GROUP BY item_product_code, item_cost_price;

This is the result I'm getting.

(I only require the first of any duplicate product code, ommitting any further entries...)

item_product_code	item_cost_price
1007	6.35
1008	4.75
1016	4.95
1016	6.95
1018	6.25
1018	6.95
1022	9.95
1023	3.95
1023	6.50
1026	7.50