Sql-server – Min function returning too many rows

querysql server

Apologies in advance if I am asking in the wrong forum.

I am trying to get the first attribute value for each ID in my table based on the ID's starting week. When using the min() function on weekkey, my query is returning more than one value if an ID has more than one attribute.

Drop table if Exists table1

CREATE TABLE table1 (
  IDNumber INT NOT NULL,
  Attribute VARCHAR(30) NOT NULL,
  WeekKey INT NOT NULL,
  );

INSERT INTO table1 
    (IDNumber, Attribute, WeekKey) 
VALUES 
    (1,'a',1),
    (1,'a',2),
    (1,'b',3),
    (2,'c',1),
    (3,'a',1),
    (3,'c',2);

Select IDNumber, Attribute, Min(WeekKey) StartWeek
From Table1
Group by IDNumber, Attribute
Order by IDNumber, Attribute

My goal output would be:

1  a  1
2  c  1
3  a  1

My current output is:

1  a  1
1  b  3
2  c  1
3  a  1
3  c  2

Does anyone have an idea how to accomplish my goal without too many extra steps?

Thanks,
Taylor

Best Answer

You need to use a window function like ROW_NUMBER() to generate a unique ID sorted on the WeekKey partitioned by IDNumber.

WITH CTE_Table1_Sorted AS -- CTE of the table1 dataset with a SortId generated from the order of the WeekKey field
(
   SELECT IDNumber, Attribute, WeekKey, ROW_NUMBER() OVER (PARTITION BY IDNumber ORDER BY WeekKey) AS SortId -- Generates a unique ID for every record, in the order of WeekKey, from least to greatest
   FROM table1
)

SELECT IDNumber, Attribute, WeekKey AS StartWeek
FROM CTE_Table1_Sorted
WHERE SortId = 1 -- Filter out every other record except the one with the least WeekKey, per IDNumber
Order by IDNumber
Related Question