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



Print Page | Close Window

Forum Software by Web Wiz Forums® version 12.08 - https://www.webwizforums.com
Copyright ©2001-2026 Web Wiz Ltd. - https://www.webwiz.net