CODEWITHSUNDEEP
sql-server
Danicco
Danicco

Reputation: 1673

SQL query add custom value to select based on distinct column value

I have this table from a subquery select:

Name    ResultType    TestID
Guy1    Result A      1
Guy1    Result B      1
Guy1    Result C      1
Guy1    Result A      2
Guy1    Result C      2
Guy1    Result B      3
Guy2    Result A      4
Guy2    Result C      4
Guy3    Result D      5
Guy3    Result A      6
--ETC

A list of testIDs, results they got and the name of the user. I want to be able to get this in the SELECT statement:

Name    ResultType    TestID    WhichTestNumberWasIt
Guy1    Result A      1         1
Guy1    Result B      1         1
Guy1    Result C      1         1 --The first distinct TestID for this user
Guy1    Result A      2         2
Guy1    Result C      2         2 --The second
Guy1    Result B      3         3 --And third
Guy2    Result A      4         1 --This is the first distinct TestID for this user
Guy2    Result C      4         1 
Guy3    Result D      5         1 --Again, same distinct TestID for another user
Guy3    Result A      6         2
--ETC

Preferably during the SELECT statement because this is a subquery from a JOIN that's inside a () and it's quite sizeable already (I omitted a bunch of columns here).

Is there any way to do this?

Upvotes: 1

Views: 2095

Answers (2)

GarethD
GarethD

Reputation: 69819

You can use DENSE_RANK to get this:

SELECT  Name,
        ResultType,
        TestID,
        TestNumber = DENSE_RANK() OVER(PARTITION BY Name ORDER BY TestID)
FROM    <Subquery>;

Example on SQL Fiddle

Full Example

IF OBJECT_ID(N'tempdb..#T', 'U') IS NOT NULL DROP TABLE #T;

CREATE TABLE #T ([Name] VARCHAR(4), [ResultType] VARCHAR(8), [TestID] INT)
INSERT INTO #T ([Name], [ResultType], [TestID])
VALUES
    ('Guy1', 'Result A', 1),
    ('Guy1', 'Result B', 1),
    ('Guy1', 'Result C', 1),
    ('Guy1', 'Result A', 2),
    ('Guy1', 'Result C', 2),
    ('Guy1', 'Result B', 3),
    ('Guy2', 'Result A', 4),
    ('Guy2', 'Result C', 4),
    ('Guy3', 'Result D', 5),
    ('Guy3', 'Result A', 6);

SELECT  Name,
        ResultType,
        TestID,
        TestNumber = DENSE_RANK() OVER(PARTITION BY Name ORDER BY TestID)
FROM    #T
ORDER BY Name, TestID;

Upvotes: 2

Sanjay Goswami
Sanjay Goswami

Reputation: 1386

CREATE TABLE #tmp
(
    NAME VARCHAR(100),
    RT VARCHAR(50),
    testid INT
)

Insert into #tmp values('Guy1','Result A',1)
Insert into #tmp values('Guy1','Result B',1)
Insert into #tmp values('Guy1','Result C',1)
Insert into #tmp values('Guy1','Result A',2)
Insert into #tmp values('Guy1','Result C',2)
Insert into #tmp values('Guy1','Result B',3)
Insert into #tmp values('Guy2','Result A',4)
Insert into #tmp values('Guy2','Result C',4)
Insert into #tmp values('Guy3','Result D',5)
Insert into #tmp values('Guy3','Result A',6)


SELECT *,DENSE_RANK()   Over( PARTITION BY NAME ORDER BY TestID ASC) as Rank FROM #tmp

Upvotes: 3

Related Questions