Sequentially number each occurrence of distinct values

Question

I've got a list of job numbers and associated customer codes. Sometimes there is more than one customer per job number. Using the below link, I incorporated the answer to get the results showing the total count of occurrences of each job number as represented here:

MySQL: Count occurrences of distinct values for each row

Code:

SELECT CR_CONTRACT_MASTER1_V.Job_Number, CR_CONTRACT_MASTER1_V.CUSTOMER_CODE,
    COUNT(CR_CONTRACT_MASTER1_V.Job_Number) OVER (PARTITION BY CR_CONTRACT_MASTER1_V.Job_Number)

From CR_CONTRACT_MASTER1_V

GROUP BY CR_CONTRACT_MASTER1_V.Job_Number, CR_CONTRACT_MASTER1_V.CUSTOMER_CODE
ORDER BY 3 DESC

Results:

   21-0138  NULT01      3
   21-0138  WASAN       3
   21-0138  WMRENEW     3
   21-0204  AMA12       2
   21-0204  GSOI3       2

I don't actually know anything about what I'm doing here, I just started doing some SQL stuff a couple of days ago. Is there a way to count each occurrence in sequential order to look more like this?

   21-0138  NULT01      1
   21-0138  WASAN       2
   21-0138  WMRENEW     3
   21-0204  AMA12       1
   21-0204  GSOI3       2

I used SSMS to write this query

EDIT: Can this be arranged so that a specific customer code is listed first?

In this example data results, I want "NULT01" customer code to always be listed first like this:

   21-0138  NULT01      1
   21-0138  WASAN       2
   21-0138  WMRENEW     3
   21-0204  NULT01      1
   21-0204  GSOI3       2

The results would always show "NULT01" before any other customer codes regardless of alphabetical order.

Looks like you are asking for `row_number() over(partition by Job_number order by Customer_Code)` — Stu, Oct 19 '22 at 23:50

Tim Biegeleisen · Accepted Answer · 2022-10-20T16:40:26.903

1

Use ROW_NUMBER() instead of COUNT():

SELECT
    Job_Number,
    CUSTOMER_CODE,
    ROW_NUMBER() OVER (PARTITION BY Job_Number) AS rn
FROM CR_CONTRACT_MASTER1_V
GROUP BY
    Job_Number,
    CUSTOMER_CODE
ORDER BY
    CASE WHEN CUSTOMER_CODE LIKE 'UNHI%' THEN 1 ELSE 2 END,
    3;

edited Oct 20 '22 at 16:40

answered Oct 19 '22 at 23:51

Tim Biegeleisen

502,043
27
286
360

1

@John I have a serious tag reading dyslexia problem lately, mistaking many SQL questions for MySQL when in fact they are not. – Tim Biegeleisen Oct 20 '22 at 00:04
Thank you! Yes, ROW_NUMBER seems to have done the trick! Is there a way to prioritize which customer code gets listed first? For example, if the customer code is "UNHI", can I get that one to always be listed as the first one? – Bjorn186 Oct 20 '22 at 16:17
Please see edited post above showing desired results. – Bjorn186 Oct 20 '22 at 16:31
Sort using a `CASE` expression. – Tim Biegeleisen Oct 20 '22 at 16:40

Sequentially number each occurrence of distinct values

1 Answers1