Just a simple SELECT statement? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Just a simple SELECT statement?

Author

Topic

MikaelJ
Starting Member

2 Posts

Posted - 2003-03-27 : 05:26:47

Hello,

I need some help with a simple Select statement.
First my query:

SELECT CONTACT_NAME, CUSTOMER_NO
FROM CONTACT
JOIN CUSTOMER
ON CONTACT.OBJ_NO = CUSTOMER.OBJ_NO
ORDER BY CUSTOMER_NO, CONTACT_NAME

Result:

CONTACT_NAME CUSTOMER_NO
---------------------------------------- -----------
Elisabeth Moen 100
NR 2 100
Annika Högström 10000
NR 2 10000
Ivan Feldborg 1014
Karin H. 1014
Monica Källberg 1015
Agneta Rogström 1018
Anneli Malmhed 1018
Ingrid Dahlström 1018
Lena Bäcklin 1018
Bernt Linström 1020
Mats Österlund 1020
P-O Karlström 1020
Roger Vikbom 1020
Elisabet Gustavsson 1022
Kristina Harsbo 1022
Siv Pettersson 1022

and so on...

I want to write a select statement that brings me this result:

CONTACT_NAME CUSTOMER_NO
---------------------------------------- -----------
Elisabeth Moen 100
Annika Högström 10000
Ivan Feldborg 1014
Monica Källberg 1015
Agneta Rogström 1018
Bernt Linström 1020
Elisabet Gustavsson 1022

I want the first CONTACT_NAME in the tabel for each CUSTOMER_NO and exclude all the other contact names for that specific Customer_no.

Thanks for helping an amateur!

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-03-27 : 05:43:14

Try this:

SELECT Sub1.CONTACT_NAME, CUSTOMER_NO
FROM CUSTOMER INNER JOIN
(SELECT OBJ_NO, MIN(CONTACT_NAME) AS CONTACT_NAME
FROM CONTACT GROUP BY OBJ_NO) AS Sub1
ON Sub1.OBJ_NO = CUSTOMER.OBJ_NO
ORDER BY CUSTOMER_NO, Sub1.CONTACT_NAME

To return the first contact name for each customer, I have used the MIN() function, which will return the first entry alphabetically.

OS

MikaelJ
Starting Member

2 Posts

Posted - 2003-03-27 : 05:59:52

Thank you mohdowais!

Works just fine! Super!

(I´ve tried earlier with the MIN-function without success...)

/Mikael

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources