simple distinct query - 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)

simple distinct query

Author

Topic

Champinco
Yak Posting Veteran

54 Posts

Posted - 2006-11-19 : 19:34:16

Hi guys i need help with this
Code Name
NULL Mobile
NULL UNSPECIFIED
10 COMMUNICATIONS
10 COMMUNICAITONS
10 OFFICE COMMS

How do I select distinct codes, with the corresponding name (the 1st one or 2nd-doesn’t matter which one).
I.E:
I want this result:

Code Name
NULL Mobile
10 COMMUNICATIONS

I tried
select distinct Code, Name,
from table
But obviously this doesn’t work as Name column has different words in it.

Cheers
GK

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-19 : 19:51:20

quote:
(the 1st one or 2nd-doesn’t matter which one).

It does matter, which is why SQL Server doesn't guess. So you can pick one using whichever aggregate function you like, for example

SELECT Code, min([Name])
FROM table
GROUP BY Code

Champinco
Yak Posting Veteran

54 Posts

Posted - 2006-11-19 : 22:03:43

yes sorry this is obvious.
what i should of also indicated was that i have another column with a dollar value in it such that i cannot use a group by: i.e:

Amount Code Name
$30.55 1075 21ST CENTURY COMMUNICATION
$30.55 1075 CENTURY COMMUNICATION
$11.52 2921 A & B COMMUNICATIONS
$1934.26 5012 A & P ELECTRONICS - LET
$6.20 4073 A CUMMINGS & CO
$6.20 4073 CUMMINGS & CO
$6.20 4073 CUMMINGS and CO

Such that I keep only one record that has the same code and amount, and choose the 1st or 2nd (or 3rd etc...) name. in this scenario:

Amount Code Name
$30.55 1075 21ST CENTURY COMMUNICATION
$11.52 2921 A & B COMMUNICATIONS
$1934.26 5012 A & P ELECTRONICS - LET
$6.20 4073 A CUMMINGS & CO

Do I use some sort of count that goes through each row systematically?
Cheers
GK

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-19 : 23:25:33

Are the amounts always the same? You really should clean up the data otherwise you'll start getting all sorts of problems, but if the amount is the same for the same code then you can use min for the amount too

SELECT Code, min([Name]), min(Amount)
FROM table
GROUP BY Code

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources