Query for distinct product name

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Query for distinct product name

Author

Topic

globemast
Starting Member

32 Posts

Posted - 2006-06-28 : 11:57:07

Hello,

I have a table (PRODUCTS) which holds information about products. The columns of tha table contain amont other product_id, product_name and product_popularity.

A product with the same name can be obtained by different suppliers....

I want to query the PRODUCTS table such that i get the the product_id and product_name. I can do this just fine but it returns me duplicate entries for products with the same name.

Is there a way i can query the table and to return me distinct product_names. (Product_name is varchar(8000)).

Thank you.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-28 : 12:01:00

"Is there a way i can query the table and to return me distinct product_names."
which product_id would you want ?

select product_name, max(product_id) as [Product ID]
from   PRODUCTS
group by product_name

globemast
Starting Member

32 Posts

Posted - 2006-06-28 : 12:07:30

Basically i want to select all entries in the table, in descending order based on their popularity and also i should not have duplicate names.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-06-28 : 12:18:26

Let me get this clear...by "A product with the same name can be obtained by different suppliers" you mean there are multiple records in the suppliers table for same product_id or there are different product_id for same product_name in the product table ?

Harsh Athalye
India.
"Nothing is Impossible"

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-06-28 : 12:19:23

try


Select Distinct product_name, product_popularity
from   PRODUCTS
Order by product_popularity

Srinika

globemast
Starting Member

32 Posts

Posted - 2006-06-28 : 12:27:47

Srinika the distinct command does not work...

To make it clear this is my product table below:

Products Table:
---------------
int pr_id
varchar pr_name
int popularity

A product with the same name can be in the table under different id and with different popularity because it is supplied by different provider.

Therefore, Records in the table can have the same pr_name and different pr_id and pr_popularity.

i want to query the table for records with the highest popularity and distinct pr_names.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-06-28 : 12:42:00

Try this...

select t2.pr_name, t1.pr_id, t2.popularity
from product as t1
join
(select distinct pr_name, max(popularity) as popularity
from product
group by pr_name) as t2
on t1.popularity = t2.popularity
and t1.pr_name = t2.pr_name
order by t2.popularity

Harsh Athalye
India.
"Nothing is Impossible"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-28 : 12:43:07

This simple one?

SELECT    product_name,
          max(product_popularity)
FROM      Products
GROUP BY  product_name
ORDER BY  max(product_popularity) desc

EDIT TYPO 1: MISSED GROUP BY
EDIT TYPO 2: MISSED ,

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-06-28 : 12:46:38

Peter,
You can't do that!

since there is no group by clause, you can't use aggregate functions like max()! sorry but I wish to hell if life wud have been that simple

Harsh Athalye
India.
"Nothing is Impossible"

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-06-28 : 12:48:28

Can u please provide some sample data and expected results

Srinika

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-06-28 : 13:40:58

Here is the most godawful way to get the answer.



-- prepare table and data
declare @Products table(pr_id int, pr_name varchar(50), popularity int)

insert into @Products 
select 1,'Good Foo',25
union all
select 2,'Bad Foo',23
union all
select 3,'Extra Foo',12
union all
select 4,'Good Foo',3
union all
select 5, 'Good Bar',73
union all
select 6,'Bad Bar',22
union all
select 7,'Extra Bar',45
union all
select 8,'Bad Bar',34

-- verify data
select * from @Products

-- now, the most horrible, convoluted way to do it

declare @Cheat table(D int identity(1,1), pr_name varchar(50), popularity int)
declare @Answer table(pr_id int, pr_name varchar(50), popularity int)

insert into @Cheat 
select pr_name,max(popularity)as popularity 
from @Products 
group by pr_name

declare @ct int
,@a int, @b varchar(50), @c int

select @ct = max(D) from @Cheat

while @ct > 0
BEGIN


select @b = pr_name from @cheat where D = @ct
select @c =  popularity from @cheat where D = @ct

select @a = pr_id from @Products 
where pr_name = @b
and popularity = @c

insert into @Answer select @a,@b,@c


set @ct = @ct-1
END

select * from @Answer

Of course this (below) gives you most of the answer, just NOT the dang ID.



SELECT    
	pr_name
        ,max(popularity)
FROM      
	@Products
GROUP BY 
	pr_name

So, lets go Peter and Nigel, lets see a NICE answer

Help us help YOU!
Read this blog entry for more details: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

*need more coffee*
SELECT * FROM Users WHERE CLUE > 0
(0 row(s) affected)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-28 : 13:44:33

quote:
Originally posted by DonAtWork

So, lets go Peter and Nigel, lets see a NICE answer

What's not nice?

quote:
Originally posted by globemast

i want to query the table for records with the highest popularity and distinct pr_names.

Peter Larsson
Helsingborg, Sweden

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-06-29 : 03:22:17

How'bout this:

declare @Products table(id int, name varchar(50), pop int)

insert into @Products 
select 1, 'Good Foo', 25  union all
select 2, 'Bad Foo', 23   union all
select 3, 'Extra Foo', 12 union all
select 4, 'Good Foo', 3   union all
select 5, 'Good Bar', 73  union all
select 6, 'Bad Bar', 22   union all
select 7, 'Extra Bar', 45 union all
select 8, 'Bad Bar', 34

-- verify data
select * from @Products

SELECT 
  (SELECT TOP 1 ID FROM @Products WHERE name = a.name AND pop = MAX(a.pop)) AS ID,
  name, 
  MAX(pop) AS Popularity
FROM @Products a
GROUP BY name
ORDER BY Popularity DESC

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-29 : 05:35:20

quote:
Originally posted by harsh_athalye

Peter, You can't do that!

You're right. I have corrected the typo now.

Peter Larsson
Helsingborg, Sweden

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2006-06-29 : 05:49:32

quote:
Originally posted by Peso

This simple one?
SELECT product_name, --<-- added the ,
max(product_popularity)
FROM Products
GROUP BY product_name
ORDER BY max(product_popularity) desc

EDIT TYPO: MISSED GROUP BY

Peter Larsson
Helsingborg, Sweden

you need the ,

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-29 : 07:05:39

quote:
Originally posted by Wanderer

[quote]Originally posted by Peso

This simple one?
SELECT product_name, --<-- added the ,
you need the ,

I left that one as an exercise to the original poster

Just kidding. Thank you.

Peter Larsson
Helsingborg, Sweden

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-06-29 : 07:07:10

oh, Harsh Athalye had it nailed the first time. I just wanted to see HOW UGLY i could do it.

I suppose i could have made it a cursor instead....

Oh, and nice one Lumbago!

Help us help YOU!
Read this blog entry for more details: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

*need more coffee*
SELECT * FROM Users WHERE CLUE > 0
(0 row(s) affected)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-29 : 07:09:31

quote:
Originally posted by harsh_athalye

Peter,
You can't do that!

since there is no group by clause, you can't use aggregate functions like max()!

Actually you can. But you're right, not in this example.
Otherwise you can use

DECLARE @Value INT

SELECT @Value = MAX(AnyNumericField)
FROM AnyTable

Look, no GROUP BY...

Peter Larsson
Helsingborg, Sweden

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2006-06-29 : 07:18:13

wonder what the different solutions performances are like....

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-06-29 : 11:38:36

i claim worst performance. dont MAKE me use a linkserver !!!

Help us help YOU!
Read this blog entry for more details: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

*need more coffee*
SELECT * FROM Users WHERE CLUE > 0
(0 row(s) affected)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-30 : 04:28:40

quote:
Originally posted by DonAtWork

i claim worst performance. dont MAKE me use a linkserver !!!

If you do that, I promise I will throw a CURSOR solution at you

Peter Larsson
Helsingborg, Sweden

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources