CODEWITHSUNDEEP
sqlsql-servergroup-bydistinct
Sadjad Johansson
Sadjad Johansson

Reputation: 320

How to select distinct column from my table?

I have two tables that I work with. The first table #CustomerList I manually initialize with values. Then I create a second table #CustomerListWithRank_Id that is based on the first first table #CustomerList.

The only different between the first and second table is that the second table #CustomerListWithRank_Id has a column that gives each row it's rownumber which I use further down in my SQL code when I loop through that table.

So the problem that I have is that I just want to initialize #CustomerListWithRank_Id with only unique values from the column [FileName], how do I do that?

SELECT '3004' Customer_No,'831001' DDD_Reference_No,'varer831.csv' [FileName]
INTO #CustomerList -- DROP TABLE #CustomerList
UNION ALL SELECT '3002' Customer_No,'199001' DDD_Reference_No,'varer199.csv' [FileName]
UNION ALL SELECT '3003' Customer_No,'199002' DDD_Reference_No,'varer199.csv' [FileName]

-

SELECT *, ROW_NUMBER() OVER (ORDER BY Customer_No) AS 'Rank_Id'
INTO #CustomerListWithRank_Id --DROP TABLE #CUstomerListWithRank_ID
FROM #CustomerList

If I use GROUP BY [FileName] in the query above I get this error:

Msg 8120, Level 16, State 1, Line 1
Column '#CustomerList.Customer_No' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Upvotes: 0

Views: 65

Answers (2)

umer
umer

Reputation: 1316

is this what you need 

SELECT * INTO #CustomerListWithRank_Id From
(SELECT *, ROW_NUMBER() OVER (PARTITION BY FileName Order by Customer_No)  AS RowNum FROM CustomerList)

Upvotes: 0

shurik
shurik

Reputation: 805

Is this what you need?

select *, ROW_NUMBER() OVER (ORDER BY Customer_No) AS 'Rank_Id'
INTO #CustomerListWithRank_Id 
from
(
    SELECT max(Customer_No) Customer_No, max(DDD_Reference_No) DDD_Reference_No, FileName
    FROM #CustomerList
    group by FileName
) t

Upvotes: 1

Related Questions