DISTINCT question - 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

SQL Server Development (2000)

DISTINCT question

Author

Topic

kelleyb
Yak Posting Veteran

61 Posts

Posted - 2004-03-10 : 08:35:47

Stay unemployed for a few years and suddenly those 500 line stored procedures you had written years ago -- when your skills were sharp -- start to look like Chinese.


SELECT DISTINCT * FROM ##TEMP_SICCodes WHERE LEFT(SIC,2) = '53'

Returns the following result set

SIC SIC Description
---- ----------------------------------------------------------------
53 General merchandise stores
5311 Department Stores
5331 Variety Stores
5399 Miscellaneous General Merchandise Stores
5399 All Other General Merchandise Stores
5399 Warehouse Clubs and General Merchandise Combination Stores

How do I use a distinct that will return only distinct SIC's but will also include the SIC Description so that the result set looks like this (i.e. it returns only the first SIC it encounters for number 5399)...

SIC SIC Description
---- ----------------------------------------------------------------
53 General merchandise stores
5311 Department Stores
5331 Variety Stores
5399 Miscellaneous General Merchandise Stores

-Brian

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2004-03-10 : 08:41:15

Yes there another column in the table to indicate the active SIC. Otherwise how do you tell which one is current.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-03-10 : 09:11:56

1. Throw the result set into a table variable with an identity column.

2. Get the min(id), sic from that.

3. Return the result set joining the temp table to result in step 2.

Kind of a long way to get to an answer. If I had to do it a lot, I would be writing a table-based solution.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

kelleyb
Yak Posting Veteran

61 Posts

Posted - 2004-03-10 : 10:09:26

Derrick, Valter thanks for your suggestions. Valter helped me on the right path (before I read your post Derrick) with the common sense that I needed to choose another uniquely identifying value. I have the data that I need now, but I'd be grateful if you could give me your comments on the statements I wrote. I'm just getting back into this after a couple of years and I'm trying very hard to remember everything that was, at one time, second nature.

Thanks again,
Brian

PS: "SIC" stands for "Standard Industry Classification". It's used to define American business categories.


/*	Import SIC codes and modify the codes of primary categories		*/

SET NOCOUNT ON
if exists (select * from Tempdb..sysobjects where name LIKE N'##TEMP_SICCodes%') drop table TempDB.##TEMP_SICCodes
CREATE TABLE [dbo].[##TEMP_SICCodes] (
	[SIC] varchar(4) NULL,
	[Part] varchar(4) NULL,
	[Break] varchar(4) NULL,
	[SIC Description] [varchar] (512) NULL
	)
INSERT INTO ##TEMP_SICCodes (SIC, Part, [Break], [SIC Description])
SELECT SIC, [Part], [Break], [SIC Description]
FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source=naicstb2.xls;Extended Properties=Excel 8.0')...[naicstb2$]
WHERE SIC IS NOT NULL
PRINT CAST(@@ROWCOUNT AS varchar) + ' records imported from spreadsheet'
UPDATE ##TEMP_SICCodes SET SIC = '0' + (SIC) WHERE LEN(SIC) = 1
PRINT CAST(@@ROWCOUNT AS varchar) + ' records changed to add leading zero to single digit SIC''s'
UPDATE ##TEMP_SICCodes SET SIC = (SIC) + '00' WHERE LEN(SIC) = 2
PRINT CAST(@@ROWCOUNT AS varchar) + ' records changed to add two trailing zero''s to double digit SIC''s'

/*	Create SICPri table from top level categories		*/

if exists (select * from dbo.sysobjects where id = object_id(N'[SICPri]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [SICPri]
CREATE TABLE [dbo].[SICPri] (
	[SIC] [char] (2) NOT NULL PRIMARY KEY,
	[SICDesc] [varchar] (512)
	)
INSERT INTO SICPri (SIC, SICDesc)
SELECT DISTINCT LEFT(SIC,2) AS 'SIC', [SIC Description] AS 'SICDesc'
FROM ##TEMP_SICCodes
WHERE [SIC] IS NOT NULL AND RIGHT([SIC],2) = '00'
PRINT CAST(@@ROWCOUNT AS varchar) + ' records imported from temp table to SICPri table'

/*	Create SICSec table with all UNIQUE second level categories		*/

if exists (select * from dbo.sysobjects where id = object_id(N'[SICSec]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [SICSec]
CREATE TABLE [dbo].[SICSec] (
	[SIC] [char] (2) NOT NULL,
	[SICSec] [char] (2) NOT NULL,
	[SICDesc] [varchar] (512) NOT NULL
	)
INSERT INTO SICSec (SIC, SICSec, SICDesc)
SELECT DISTINCT LEFT(SIC,2) AS 'SIC', RIGHT(SIC,2) AS 'SICSec', [SIC Description] AS 'SICDesc'
FROM ##TEMP_SICCodes
WHERE RIGHT([SIC],2) != '00' AND [Part] IS NULL AND [Break] IS NULL
PRINT CAST(@@ROWCOUNT AS varchar) + ' records imported from temp table to secondary SIC table'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources