Select DISTINCT, except for one field in the select.

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)

Select DISTINCT, except for one field in the select.

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-03-28 : 08:02:23

Pedro writes "Hello,

I need to select distinct records, except for one field in the record.

For example... I'm trying to select the following.

- CompanyCode
- Publication
- Date
- AdCode (but I want to exclude AdCode from the distinct)

Right now if I select records I would get.

CompanyOne - USA TODAY - 03/22/02 - 1
CompanyOne - USA TODAY - 03/22/02 - 2

I need to select just one of them. It doesn't matter which one.

Any feedback would be greatly appreciated.

Thanks"

davidpardoe
Constraint Violating Yak Guru

324 Posts

Posted - 2002-03-28 : 08:51:20

SELECT CompanyCode, Publication, Date, MIN(AdCode)
FROM tablename
GROUP BY CompanyCode, Publication, Date

The MIN is arbitrary - could be any aggregate function if it truly does not matter which AdCode you need. If you don't need an AdCode just do SELECT DISTINCT CompanyCode, Publication, Date...

============================
Chairman of
The NULL Appreciation Society
"Keep NULLs as NULL"

Edited by - davidpardoe on 03/28/2002 08:52:10

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources