Distinct BUT NOT Whole ROW - 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 BUT NOT Whole ROW

Author

Topic

lloydsj
Starting Member

7 Posts

Posted - 2003-04-24 : 06:32:37

Hi,

Basically I have got a table with a few fields in it, and I want to return a few of the fields (but 1 of them must be unique... in the example field a). IE. I want to make sure that 1 of the fields returns all the unique items that it contains, and then the other fields are just displayed along side it.

If i do something like...

SELECT DISTINCT a, b, c
FROM table
WHERE (a IS NOT NULL)

... it returns all DISTINCT occurances of complete rows. Meaning field a (or any of the fields on their own may not be unique). How do I do this? I have got a feeling its to do with Inner or Outer Join, but i can figure it out.

Cheers,
Stu

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2003-04-24 : 07:55:37

post some sample date and some sample expected results.....

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-04-24 : 08:10:25


select a,b,c
from table
group by a,b,c

Jay White
{0}

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-24 : 12:15:11

Need to be more clear here, but I'm thinking he's askin that he only wants unique values for each column, not the composite, in one result set. WHY? I can't even begin to fathom...but if that's it then:


SELECT col1, col2, col3
  FROM (   SELECT DISTINCT col1, null as col2, null as col3
	     FROM Table1
	UNION ALL
	   SELECT DISTINCT null as col1, col2, null as col3
	     FROM Table1
	UNION ALL
	   SELECT DISTINCT null as col1, null as col2, col3
	     FROM Table1
	) As xxx

But that doesn't make a lot of sense either...

Any clue as to what you're trying to accomplish?

Brett

8-)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-24 : 12:26:01

You want to GROUP BY the field that should be unique ... but then, you can no longer display all of the other fields because logically that makes no sense.

For example, if you're data is:

1,2
1,3
2,2
3,3
3,5

and you want only unique numbers in the first field, so only 3 records are returned:

1
2
3

But ... you say you want to see the other field as well. But how? what do you want to see? 1 was matched up with 2 and 3 -- you can't return them both, because you said you only want distinct rows for the first field! so which do you want? if you want the MIN() or the MAX(), that is no problem:

Select Field1, MAX(Field2) as Field2Max, MIN(field2) as Field2Min
FROM Table
GROUP BY Field1

If the second field is a numeric field and you just want a TOTAL of that field, or the AVG, you can do this:

select field1, SUM(Field2) as Field2Sum, AVG(Field2) as Field2AVG
from table
group by field1

But if you want to SEE all values in the other fields, the best you can do is return a list of the other fields seperated by comma's or something like that -- but that can be very tricky in SQL. search this site for "CSV" or "building comma-seperated string" or things of that nature for some techniques. but at that point, you are returing a long string of data all concatenated together, not actual fields from the table ...

I hope this makes some sense. But as Andrew asked, give us some sample data and what you want to return.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources