Unit:
    UnitId int PK
    Title varchar
    
    UnitOption:
    UnitOptionId int PK
    UnitId int FK
    Title varchar
    
    Quote:
    QuoteId int PK
    UnitOptionId int FK
    Title varchar

    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 ''
    */

quote:

---

Originally posted by weitzhandler

I found the answer myself.

It's the [url http://msdn.microsoft.com/en-us/library/ms186734.aspx]ROW_NUMBER()[/url] function, then, using a [url http://msdn.microsoft.com/en-us/library/ms175972.aspx]WITH[/url] statement and a [url http://msdn.microsoft.com/en-us/library/ms181765.aspx]CASE[/url] statement I make up my varchar result.


Shimmy

---