Simple SELET problem - 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

General SQL Server Forums

New to SQL Server Programming

Simple SELET problem

Author

Topic

funk.phenomena
Posting Yak Master

121 Posts

Posted - 2012-07-27 : 12:25:57

Hi All - I have the following table:

[CODE]
CUSTOMER_NUM CITY
123456 NEW YORK
123456 BOSTON
123456 MIAMI
123456 NULL
123457 NULL
123457 LOS ANGELES
123457 CHICAGO
123458 NEW YORK
123458 JUNEAU
123458 ALASKA
123458 BILLINGS
[/CODE]

There are repeated customer numbers, with multiple cities. I'd like my query to output a unique customer number, and the just first NOT NULL city it can find in the database, as follows:
[code]
CUSTOMER_NUM CITY
123456 NEW YORK
123457 LOS ANGELES
123458 JUNEAU
[/CODE]

How can I acheive this? Thanks!!

[CODE]
SELECT CUSTOMER_NUM, CITY FROM TABLE1
WHERE CITY IS NOT NULL
GROUP BY CUSTOMER_NUM, CITY

[/CODE]

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-27 : 12:33:03

You can use an aggregate function such as MIN:

SELECT CUSTOMER_NUM, MIN(CITY) AS CITY FROM TABLE1
WHERE CITY IS NOT NULL
GROUP BY CUSTOMER_NUM

That will give you the city which is first in alphabetical sort.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-27 : 12:33:36

there's no concept of first or last in sql table unless you specify it by means of another column. do you've unique valued column to determine the order?
otherwise order of retrieval is not guaranteed


SELECT CUSTOMER_NUM,CITY
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY CUSTOMER_NUM ORDER BY CUSTOMER_NUM) AS Seq,*
FROM Table
WHERE CITY IS NOT NULL
)t
WHERE Seq=1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

funk.phenomena
Posting Yak Master

121 Posts

Posted - 2012-07-27 : 12:55:59

Hi Sunita - Aggregate functions like MIN/MAX only seem to work with numeric data types, unless I'm missing something?
Hi Visakh - Worked like a charm :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-27 : 13:02:37

quote:
Originally posted by funk.phenomena

Hi Sunita - Aggregate functions like MIN/MAX only seem to work with numeric data types, unless I'm missing something?
Hi Visakh - Worked like a charm :)

welcome

Nope thats wrong. it works with string data too and returns first or last value taken in alphabetical order

see


SELECT MIN(val),MAX(val)
FROM
(
SELECT 'aaa' AS val UNION ALL
SELECT 'ba12' AS val UNION ALL
SELECT 'a21aa' AS val UNION ALL
SELECT 'akgc' AS val UNION ALL
SELECT 'ch347de5'
)t

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources