CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Ccorock
Ccorock

Reputation: 892

SQL Server Stored Procedure SELECT DISTINCT

I'm working on deciphering some stored procedures and have minimal vocabulary on the subject. Can someone please explain to me what role this '1' serves in the below statement? I can not find any DISTINCT syntax tutorials to explain this. I'm referring to the actual "1" one in the statement.

USE TEST
GO
SET ANSI_NULLS, QUOTED_IDENTIFIER ON
GO

 CREATE PROCEDURE [dbo].sp_F_SQL 
 (@Id int) 
 WITH ENCRYPTION AS  
 SELECT DISTINCT 
    dbo.MAP_SQL.rID, 
    dbo.MAP_SQL.lID, 
    dbo.MAP_SQL.cID, 
    **1** as RESPFACT, 
    dbo.MAP_SQL.Longitude, 
    dbo.MAP_SQL.Latitude, 
    dbo.MAP_SQL.Altitude,

...

Upvotes: 0

Views: 9507

Answers (2)

D Stanley
D Stanley

Reputation: 152596

The 1 has nothing to do with DISTINCT. It just adds an output column titled RESPFACT that has a value of 1 for all rows. I suspect whatever is consuming the output need that column.

SELECT DISTINCT only returns the "distinct" rows from the output - meaning rows where ALL column values are equal.

e.g. if your output without distinct was

1   2   ABC   DEF
2   3   GHI   JLK
2   1   ABC   DEF
1   2   ABC   DEF

Then rows 1 and 4 would be seen as "equal" and ony one would be returned:

1   2   ABC   DEF
2   3   GHI   JLK
2   1   ABC   DEF

Note that rows 1 and 3 are NOT equal even though 3 of the 4 column values match.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270401

The 1 generates a column called RESPFACT. This always has the value of 1.

I cannot say why this is important for the sp_F_SQL procedure.

The distinct returns unique rows. If there are duplicate values for the columns in the select then only one row is returned. Clearly, the RESPFACT column is the same in all rows, so it does not affect the rows being returned.

Upvotes: 2

Related Questions