DISTINCT? - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

DISTINCT?

Author

Topic

jamkelvl
Starting Member

5 Posts

Posted - 2009-05-08 : 11:10:15

Hey,

I'm pretty new to this but here is what I'm trying to do...

SELECT * FROM table WHERE setname like '%bike%'

This will of course return all columns in the table and will return lets say... 20 rows where the setname is the same but the rest of the coumns are different.

What I want to do is:

Return only distinct setnames and the columns that go with that particular setname.

I'm sure I need to use some kind of join or something but...

I've only been doing SQL for like 4 months so I'm pretty new.

I'm doing this for a 'search engine' being written in php...

Reason I want to do this is to eliminate/filter some of the results

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-08 : 11:17:15

can you post some sample data to illustrate what you want? do you mean only distinct set of setnames along with single associated value for other fields?

jamkelvl
Starting Member

5 Posts

Posted - 2009-05-08 : 11:24:53

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-05-08 : 11:38:27

if it doesn't matter,

select 
	SETNAME ,
	min(DESIGNNAME),
	min(CATMAJOR)
from
	yourtable
group by
	SETNAME

jamkelvl
Starting Member

5 Posts

Posted - 2009-05-08 : 11:46:58

quote:
Originally posted by sakets_2000

if it doesn't matter,
select
SETNAME ,
min(DESIGNNAME),
min(CATMAJOR)
from
yourtable
group by
SETNAME

The most important part is the WHERE setname LIKE %bike% when I add that, I get only one result. In my case, I need 9 results (all different setnames LIKE %bike%) Instead of 150++ results.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-05-08 : 11:48:54

i just gave you what you needed if DESIGNNAME, CATMAJOR columns do not matter.. Add/Delete to that to suit your requirement.

jamkelvl
Starting Member

5 Posts

Posted - 2009-05-08 : 11:55:57

Well it's not what I needed as it only returns 1 result.

What I need will return the same as:

SELECT DISTINCT setname
FROM table
WHERE setname LIKE '%bike%'

The only difference will be that it also returns all other columns associated with the setname...

It matters that all other columns show up.. This is what I need:

SELECT setname, min(designname), min(catmajor), min(seasons), min(animals), min(events), min(tags)
FROM finaldesigns
WHERE setname LIKE '%bike%';

But, this only returns 1 result.

jamkelvl
Starting Member

5 Posts

Posted - 2009-05-08 : 12:37:16

SELECT *
FROM table
WHERE setname LIKE '%bike%'
GROUP BY setname;

Okay thanks for the help..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-08 : 12:41:21

[code]SELECT f.setname,designname,catmajor, seasons, animals, events, tags
FROM finaldesigns f
JOIN (SELECT setname,MIN(designname) AS mindesign
FROM finaldesigns
GROUP BY setname) f1
ON f1.setname=f.setname
AND f1.mindesign=f.designname
WHERE f.setname LIKE '%bike%'
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources