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

SQL Server 2000 Forums

Transact-SQL (2000)

DISTINCT

Author

Topic

dcarva
Posting Yak Master

140 Posts

Posted - 2003-09-29 : 23:46:03

Hello, is it possible to use DISTINCT with a combination of fields?

SELECT DISTINCT (Field1, Fields2), Field3 FROM Table1

I want it to only get distinct field based on the first two columns, not including the 3rd. Possible?

Thanks

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2003-09-30 : 00:33:52

but then there could be more values of field3 than dictinct(field1, field2) - so how would that work?

Could you explain what you're trying to achieve?

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

dcarva
Posting Yak Master

140 Posts

Posted - 2003-09-30 : 00:53:27

Sure.

I have a query that returns a productid and it's category.

ProdId Category
------ --------
1 2
1 2
2 3
2 3

Thanks the distinct clause, I can return it like this:

ProdId Category
------ --------
1 2
2 3

Great. But now I need to return category2 field as well as show below:

ProdId Category Category2
------ -------- ---------
1 2 1
1 2 2
2 3 1
2 3 2

But notice that since the 3rd field causes the combinations to be unique, I now get the mutiple records.

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2003-09-30 : 01:03:23

oooooooohhhhhh! in that case, you want

select distinct prodid, category, category2
from prods

ie - no brackets
this will give you every distinct combination of all three fields.

Cheers

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

dcarva
Posting Yak Master

140 Posts

Posted - 2003-09-30 : 01:39:00

I apologize. I am not being very clear. This query returns the records as I want:

select distinct prodid, category

Id Category
-----------
1 2
2 1
3 2

But when I add the extra field to the end, now
shows up as:

select distinct prodid, category, category2

Id Category Category2
---------------------
1 2 1
1 2 2
1 2 3
2 1 1
2 1 2

Because the category2 field is causing rows with duplicate id/category to become distinct. But I need that field there. So I was hoping to return all 3 fields but remove any recs with duplicate Id fields.

Sorry if this doesn't make sense.
Thanks

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2003-09-30 : 01:41:04

OK - I'm confused: how about you just show me the output you want?

ie - given

create table Product(ProdId integer, Category Integer, Category2 Integer)
insert into Product(ProdId, Category, Category2) values( 1 , 2, 1)
insert into Product(ProdId, Category, Category2) values( 1 , 2, 2)
insert into Product(ProdId, Category, Category2) values( 1 , 2, 3)
insert into Product(ProdId, Category, Category2) values( 2 , 3, 1)
insert into Product(ProdId, Category, Category2) values( 2 , 3, 2)
insert into Product(ProdId, Category, Category2) values( 2 , 3, 1)
insert into Product(ProdId, Category, Category2) values( 2 , 3, 2)
insert into Product(ProdId, Category, Category2) values( 2 , 3, 3)

you want to produce ?

(or alternatively provide your own data and output)

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-30 : 07:48:10

Why is

1 2 2

returned instead of

1 2 1

or

1 2 3

?? Do you see you need to specify the reason why that should be returned instead of the others? The computer can't just know what you want, you need to tell it.

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-30 : 10:39:57

Since it seems so arbitrary...

SELECT Col1, Col2, MAX(Col3)
FROM Table1
GROUP BY Col1, Col2

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

dcarva
Posting Yak Master

140 Posts

Posted - 2003-09-30 : 10:46:21

Cool. That does it!

Again, sorry I didn't seem to explain my question very clearly.

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2003-09-30 : 19:25:57

Oh well - glad you're happy - I'm still confused.

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources