Get index of row within a group?

Question

Hello!

I have two tables:

Unit:
UnitId int PK
Title varchar

UnitOption:
UnitOptionId int PK
UnitId int FK
Title varchar

Quote:
QuoteId int PK
UnitOptionId int FK
Title varchar

I want to create a scalar UDF that takes a QuoteId param and returns a varchar that contains the following description (pseudu):

Quote.Title + '-' + Unit.Title + '-' + Unit.UnitId + 
/* Here is where my question is:
If there are more than 1 UnitOption under this Unit, then
  return '-' + the UnitOption number under this Unit
  (i.e.) if under this Unit, there are 3 UnitOption with IDs 13, 17, 55
  under the unit, and the current Quote.UnitOptionId is the 17 one,
  it should return 2.
  Which means I want to retrieve an ID of this row in the group.
Else
  return ''
*/

Answer 1

If you're using SQL 2005 or later and I've interpreted your question correctly, you should be able to adapt the following into your function.

WITH [UnitExt] AS
(
    SELECT
        [Unit].[UnitId],
        [Unit].[Title],
        COUNT(*) AS [Count]
    FROM [Unit]
        INNER JOIN [UnitOption] ON [UnitOption].[UnitId] = [Unit].[UnitId]
    GROUP BY
        [Unit].[UnitId],
        [Unit].[Title]
)

SELECT
    [Quote].[Title] + '-' + [UnitExt].[Title] + '-' + [UnitExt].[UnitId] + 
        CASE
          WHEN [UnitExt].[Count] > 1 THEN '-' +
              CAST([UnitOption].[UnitOptionId] AS varchar(max))
          ELSE ''
      END
FROM [Quote]
    INNER JOIN [UnitOption] ON [UnitOption].[UnitOptionId] =
        [Quote].[UnitOptionId]
    INNER JOIN [UnitExt] ON [UnitExt].[UnitId] = [UnitOption].[UnitId]
WHERE [Quote].[QuoteId] = @QuoteId

Answer 2

Something like this should do it.

SELECT DISTINCT Quote.Title +
       ' - ' + Unit.Title +
       ' - ' + Unit.UnitId +
       CASE
           WHEN COUNT(*) OVER(PARTITION BY Quote.Id) > 0
       THEN
           ' - ' + CAST(ROW_NUMBER() OVER (PARTITION BY Quote.Id ORDER BY Quote.UnitOptionId) AS varchar)
       ELSE
           ''
       END
  FROM Quote
  JOIN UnitOption ON UnitOption.Id = Quote.UnitOptionId
  JOIN Unit ON Unit.Id = UnitOption.UnitId
 WHERE Quote.Id = @QuoteId