Returning Multiple Columns Using SELECT DISTINCT

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)

Returning Multiple Columns Using SELECT DISTINCT

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-04-02 : 08:55:53

Robert writes "Probably a easy one here. I want to select distinct client id

(181 records with 6 clients as the clientid in SOME of the records along with other records with NULL as clientid)

from a table, but I also need to use the idnum(increment) to order by.

When I run the query with a maxrows = 15 :

select distinct clientid,idnum
from ath
where clientid is not null
group by clientid
order by idnum

I get 15 records with multiple clientid's returned in the dataset. I just want 6 records returned ordered by the
idnum

I'm using SQL 7 on BackOffice 4.5.

Am I just missing something here???

TIA

Robert"

Nazim
A custom title

1408 Posts

Posted - 2002-04-02 : 09:03:32

select clientid,min(idnum)
from ath
where clientid is not null
group by clientid
order by 2

HTH

--------------------------------------------------------------

lgraz
Starting Member

7 Posts

Posted - 2002-04-02 : 09:03:41

Sure I'll be wrong, but IIRC using

"SELECT DISTINCT clientid,idnum"

will return you distinct clientid-idnum
couples (i.e.: same clientid with different
idnums)

My probably wrong 0.02€,
LGraz :)

rforsyth
Starting Member

1 Post

Posted - 2002-04-02 : 09:33:59

THANKS NAZIM! DID THE TRICK!

Robert

quote:

select clientid,min(idnum)
from ath
where clientid is not null
group by clientid
order by 2

HTH

--------------------------------------------------------------

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources