Weird Distinct Output
Michael Lugasy
and to order it using Product_promo (int) and the length of Product_name
BIGGER int on Product_promo AND shorter name means better product!
For some reason, the following query:
SELECT DISTINCT
Product_name,Product_title,Product_description,Product_promo
FROM Products WHERE Product_category=#Val(url.Category_id)#
ORDER by Product_promo DESC, len(Product_name)
doesn't give DISTINCT Product_name!
1. What am I doing wrong?
2. Is there a way also to limit the DISTINCT query to 10 records?
Michael.
oj
1. distinct is applied to the entire row.
2. if you're on sql7/2k, you can use top...
SELECT top 10 DISTINCT
Product_name,Product_title,Product_description,Product_promo
FROM Products WHERE Product_category=#Val(url.Category_id)#
ORDER by Product_promo DESC, len(Product_name)
if you're on sql6x, then use rowcount
set rowcount 10
SELECT DISTINCT
Product_name,Product_title,Product_description,Product_promo
FROM Products WHERE Product_category=#Val(url.Category_id)#
ORDER by Product_promo DESC, len(Product_name)
set rowcount 0
--
-oj
http://rac4sql.home.attbi.com
"Michael Lugasy" <mic...@netvision.net.il> wrote in message
news:7aeb9057.02011...@posting.google.com...
Michael Lugasy
to the following:
SELECT DISTINCT top 10
Product_name,Product_title,Product_description,Product_promo,len(Product
_name) AS Product_length
FROM Product WHERE category_id=80
ORDER by Product_promo DESC, Product_length
Still - I can't get DISTINCT Product_name.
I want rows which are distinct ONLY in Product_name
and not in the "whole row", but still - to have
all other fields (Product_title/description/promo...) selected. is it
possible?
Michael.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Dieter Buecherl
which row should the server select for you in the following example:
Product_name, Product_title, ....
Name A Title 1
Name A Title 2
The server cannot make the selection for you without the
required info, so maybe you want
select product_name, max(product_title), max(....
from product
...
or use min, avg, ...
or
select product_name, product_title, ...
from product
where product_title = (select max(product_title) from product)
and ...
if you want the product_name only once, then you must say which
row; it's simply the concept of relational database
HTH
Dieter
"Michael Lugasy" <mic...@netvision.net.il> schrieb im Newsbeitrag
news:3c46ab32$0$183$7586...@news.frii.com...
Michael Lugasy
X), there are plenty - I only need the top 10 records, sorted by my
relevency.
You're saying that's ain't possible?
John Narofsky
Question 1.
If you want the top distinct product names, use the query you have
below and remove the other columns from the list that you are
selecting. If you are getting non-distinct product names, its because
you have multiple rows for each column. You could pull some magic
with the other columns to arbitrarily get the first entry for each
product name(ask if you really want to know...its ugly) but I
recommend that you figure out a way to get to each row uniquely.
JohnNarofsky
mic...@netvision.net.il (Michael Lugasy) wrote in message news:<7aeb9057.02011...@posting.google.com>...
John Narofsky
Point 1. If you want more than just the product name coming back, you
run the risk of having more than just one row per product name coming
back. Distinct works on the entire row, bud.
Point 2. set rowcount is nice. Can't use top with the distinct
keyword.
JohnNarofsky
Michael Lugasy <mic...@netvision.net.il> wrote in message news:<3c46bb24$0$190$7586...@news.frii.com>...
Chad Gould
> X), there are plenty - I only need the top 10 records, sorted by my
> relevency.
> You're saying that's ain't possible?
> Michael.
It should be possible using subqueries. Something like this:
SELECT DISTINCT
Product_name,Product_title,Product_description,Product_promo
FROM Products
WHERE Product_name In
(SELECT DISTINCT TOP 10 Product_name FROM Products WHERE Product_category=#Val
(url.Category_id)# ORDER by Product_promo DESC, len(Product_name))