How to get the index of a column's occurrence in a query?

Question

I am trying to get the index / row number of a column's occurrence in a data set to produce result alike the following:

| Make        | Model    | Option    | Model Index |
├-------------+----------+-----------+-------------┤
| Lamborghini | Diablo   | SE30 Jota | 1           |
| Lamborghini | Diablo   | SE30      | 1           |
| Lamborghini | Cala     |           | 2           |
| Pontiac     | Trans AM | GTA       | 1           |
| Pontiac     | Trans AM | Firefox   | 1           |
| Pontiac     | GTO      | Judge     | 2           |
| Pontiac     | Fiero    | GT        | 3           |

Note that the Model Index should repeat for cars of the same Make and Model with a differing option but should also reset when the make changes. I have managed to find the SQL to get the reset on make changes to happen but as soon as I include the option column, I'll get the wrong results.

SELECT
    Makes.Name,
    Models.Name,
    Options.Name,
    ROW_NUMBER() OVER (PARTITION BY Makes.Id ORDER BY Models.Name) [Model Index]
FROM 
    Makes
INNER JOIN  
    Models ON Models.MakeId = Makes.Id
INNER JOIN 
    Options ON Options.ModelId = Models.Id

Result from this SQL (not desired):

| Make        | Model    | Option    | Model Index |
├-------------+----------+-----------+-------------┤
| Lamborghini | Diablo   | SE30 Jota | 1           |
| Lamborghini | Diablo   | SE30      | 2           |
| Lamborghini | Cala     |           | 3           |
| Pontiac     | Trans AM | GTA       | 1           |
| Pontiac     | Trans AM | Firefox   | 2           |
| Pontiac     | GTO      | Judge     | 3           |
| Pontiac     | Fiero    | GT        | 4           |

Perhaps what I have wrong here is that I am using ROW_NUMBER in my query and I don't think the row number can repeat within a given partition. I think I need SomethingElse(PerhapsSomeReferenceToModel) OVER (PARTITION BY Makes.Id ORDER BY Models.Name) but don't know what the SomethingElse(PerhapsSomeReferenceToModel) should actually be!

Answer 1

Let me re-phrase your requirement:

• Model index resets when make changes
• Model index repeats for same model

You need RANK() instead of ROW_NUMBER() or DENSE_RANK() if the model index cannot contain gaps:

SELECT
    *,
    RANK() OVER (PARTITION BY Make ORDER BY Model) AS [Model Index],
    DENSE_RANK() OVER (PARTITION BY Make ORDER BY Model) AS [Model Index DENSE]
FROM @t
ORDER BY Make, Model

DB Fiddle

(The above example partition and order by names. In practice, you would actually do something like PARTITION BY MakeID ORDER BY ModelID).