1

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

2

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
4
  • 1
    Thank you JD! I am using MS SQL Server Dec 24 '20 at 2:20
  • @TaylorTapio Great, I can assure you the query will work just fine as is then (since MS SQL Server is my primary proficieny). (Also feel free to accept the answer if it is helpful so other users with the same question can see it as verified.)
    – J.D.
    Dec 24 '20 at 4:30
  • 1
    Thank you for the help again. With your help, I was able to fix the duplicate entry issue. Happy holidays! Dec 28 '20 at 2:30
  • @TaylorTapio Great! No problem, any further questions feel free, and I'll do my best to answer them. Happy holidays to you as well! :)
    – J.D.
    Dec 28 '20 at 2:56
1

In addition to correct answer posted by J.D., I would like to add just a having clause in your query to make it work and to accomplish your goal. Query below will get you that:

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

This is your query only however just a little addition of having clause as per your requirement. CTE and Windows functions are great thing to learn and use.

I hope this helps too.

2
  • You have my upvote, but just want to point out this only works when the minimum WeekKey in every group is 1. If a grouping has a WeekKey of 0 or another grouping doesn't have a WeekKey of 1 but does have a WeekKey of 2 both of those cases would not be included in the results.
    – J.D.
    Dec 24 '20 at 14:10
  • Ah, unfortunately in my real query, the first weekkey of 2020 would be labeled as 202001 for example. I think I will have to use the first route because the minimum weekkey varies for each IDNumber. Thank you all so much for the help! Dec 28 '20 at 1:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.