Row Count Challenge

donutman · Dec 23, 2004

The challenge is to find the most efficient way to generate a row counter within groups. I’m going to test it against my database of customers 190,000 distinct customers with 180,000 distinct customer orders (old ones have been warehoused) having 240,000 order details from 2800 products. I’m fairly sure I’ve indexed my tables properly.
The recordset that I want to generate is the top 50 distinct customers who have the most number of distinct items ordered within a single order. I’ve created example data and tables to make this fairly easy to test your SQL against, but let’s change the 50 to just top 2 for simplicity.
Here are a few special considerations:
[ul][li]If the same customer has more than one order only select the order that contains the most number of DISTINCT items ordered.[/li]
[li]The top 2 refers to the Count(Items) per customer order not to the top 2 customers consequently the final listing can have more than two customers with the same Count(Items).[/li]
[li]Use temp tables if you like, but it would be interesting to compare single query solutions against all others.[/li][/ul]
I haven’t done it yet, but my intention is to write a cursor approach for comparison. With the example data, you should get these results:
[tt]
# PDesc OId CName
1 Apple Fritter 10 Chrissie1
2 Caramel Pecal Roll 10 Chrissie1
3 Chocolate Donut 10 Chrissie1
4 Custard Bismark 10 Chrissie1
1 Caramel Pecal Roll 12 ESQuared
2 Chocolate Donut 12 ESQuared
3 Custard Bismark 12 ESQuared
1 Apple Fritter 13 SQLSister
2 Caramel Pecal Roll 13 SQLSister
3 Custard Bismark 13 SQLSister
[/tt]

Code:

[green]/*
DROP TABLE KarlsCustomer
DROP TABLE KarlsOrder
DROP TABLE KarlsOrderDetail
DROP TABLE KarlsProduct
*/
[/green][Blue]CREATE[/Blue] [Blue]TABLE[/Blue] KarlsCustomer
    [Gray]([/Gray]CId [Blue]int[/Blue] [Fuchsia]Identity[/Fuchsia] [Gray]([/Gray]1[Gray],[/Gray]1[Gray])[/Gray] [Blue]PRIMARY[/Blue] [Blue]KEY[/Blue][Gray],[/Gray]
     CName [Blue]varchar[/Blue][Gray]([/Gray]10[Gray])[/Gray][Gray])[/Gray]
[Blue]CREATE[/Blue] [Blue]TABLE[/Blue] KarlsOrder 
    [Gray]([/Gray]OId [Blue]int[/Blue] [Blue]PRIMARY[/Blue] [Blue]KEY[/Blue][Gray],[/Gray]
     CId [Blue]int[/Blue][Gray])[/Gray] 
[Blue]CREATE[/Blue] [Blue]TABLE[/Blue] KarlsOrderDetail
    [Gray]([/Gray]ODId [Blue]int[/Blue] [Blue]PRIMARY[/Blue] [Blue]KEY[/Blue][Gray],[/Gray]
     OId [Blue]int[/Blue][Gray],[/Gray]
     PId [Blue]int[/Blue][Gray])[/Gray]
[Blue]CREATE[/Blue] [Blue]TABLE[/Blue] KarlsProduct
    [Gray]([/Gray]PId [Blue]int[/Blue] [Fuchsia]Identity[/Fuchsia] [Gray]([/Gray]1[Gray],[/Gray]1[Gray])[/Gray] [Blue]PRIMARY[/Blue] [Blue]KEY[/Blue][Gray],[/Gray]
     PDesc [Blue]varchar[/Blue][Gray]([/Gray]20[Gray])[/Gray][Gray])[/Gray]

[Blue]INSERT[/Blue] [Blue]INTO[/Blue] KarlsCustomer
    [Blue]SELECT[/Blue] [red]'SQLSister'[/red] [Blue]UNION[/Blue]
    [Blue]SELECT[/Blue] [red]'vongrunt'[/red] [Blue]UNION[/Blue]
    [Blue]SELECT[/Blue] [red]'ESQuared'[/red] [Blue]UNION[/Blue]
    [Blue]SELECT[/Blue] [red]'Chrissie1'[/red]

[Blue]INSERT[/Blue] [Blue]INTO[/Blue] KarlsProduct
    [Blue]SELECT[/Blue] [red]'Chocolate Donut'[/red] [Blue]UNION[/Blue]
    [Blue]SELECT[/Blue] [red]'Apple Fritter'[/red] [Blue]UNION[/Blue]
    [Blue]SELECT[/Blue] [red]'Caramel Pecal Roll'[/red] [Blue]UNION[/Blue]
    [Blue]SELECT[/Blue] [red]'Custard Bismark'[/red]

[Blue]INSERT[/Blue] [Blue]INTO[/Blue] KarlsOrderDetail
    [Blue]SELECT[/Blue] 101[Gray],[/Gray]10[Gray],[/Gray]1 [Blue]UNION[/Blue]
    [Blue]SELECT[/Blue] 102[Gray],[/Gray]10[Gray],[/Gray]2 [Blue]UNION[/Blue]
    [Blue]SELECT[/Blue] 103[Gray],[/Gray]10[Gray],[/Gray]3 [Blue]UNION[/Blue]
    [Blue]SELECT[/Blue] 104[Gray],[/Gray]10[Gray],[/Gray]4 [Blue]UNION[/Blue]
    [Blue]SELECT[/Blue] 105[Gray],[/Gray]11[Gray],[/Gray]1 [Blue]UNION[/Blue]
    [Blue]SELECT[/Blue] 106[Gray],[/Gray]11[Gray],[/Gray]3 [Blue]UNION[/Blue]
    [Blue]SELECT[/Blue] 107[Gray],[/Gray]11[Gray],[/Gray]4 [Blue]UNION[/Blue]
    [Blue]SELECT[/Blue] 108[Gray],[/Gray]11[Gray],[/Gray]1 [Blue]UNION[/Blue]
    [Blue]SELECT[/Blue] 109[Gray],[/Gray]11[Gray],[/Gray]3 [Blue]UNION[/Blue]
    [Blue]SELECT[/Blue] 110[Gray],[/Gray]12[Gray],[/Gray]2 [Blue]UNION[/Blue]
    [Blue]SELECT[/Blue] 111[Gray],[/Gray]12[Gray],[/Gray]3 [Blue]UNION[/Blue]
    [Blue]SELECT[/Blue] 112[Gray],[/Gray]12[Gray],[/Gray]4 [Blue]UNION[/Blue]
    [Blue]SELECT[/Blue] 113[Gray],[/Gray]13[Gray],[/Gray]1 [Blue]UNION[/Blue]
    [Blue]SELECT[/Blue] 114[Gray],[/Gray]13[Gray],[/Gray]2 [Blue]UNION[/Blue]
    [Blue]SELECT[/Blue] 115[Gray],[/Gray]13[Gray],[/Gray]4 [Blue]UNION[/Blue]
    [Blue]SELECT[/Blue] 116[Gray],[/Gray]14[Gray],[/Gray]1 [Blue]UNION[/Blue]
    [Blue]SELECT[/Blue] 117[Gray],[/Gray]14[Gray],[/Gray]2 

[Blue]INSERT[/Blue] [Blue]INTO[/Blue] KarlsOrder
    [Blue]SELECT[/Blue] 10[Gray],[/Gray] 1 [Blue]UNION[/Blue]
    [Blue]SELECT[/Blue] 11[Gray],[/Gray] 1 [Blue]UNION[/Blue]
    [Blue]SELECT[/Blue] 12[Gray],[/Gray] 2 [Blue]UNION[/Blue]
    [Blue]SELECT[/Blue] 13[Gray],[/Gray] 3 [Blue]UNION[/Blue]
    [Blue]SELECT[/Blue] 14[Gray],[/Gray] 4

-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

ESquared · Dec 23, 2004

Strategy 1: Derived Tables

Code:

[b]-- Method DT1: two temp tables[/b]

SELECT OId, OrderSize = Count(1)
INTO #OrderSizes
FROM KarlsOrderDetail D
GROUP BY OId

SELECT CId, MaxOrderSize = Max(OrderSize)
INTO #CustomerMaxSizes
FROM #OrderSizes S
   INNER JOIN KarlsOrder O ON S.OId = O.OId
GROUP BY CId

SELECT CId
FROM #CustomerMaxSizes S
   INNER JOIN (
      SELECT DISTINCT TOP 2 MaxOrderSize FROM #CustomerMaxSizes
   ) M ON M.MaxOrderSize = S.MaxOrderSize

DROP TABLE #OrderSizes
DROP TABLE #CustomerMaxSizes

[b]-- Method DT2: one temp table[/b]

SELECT CId, MaxOrderSize = Max(OrderSize)
INTO #CustomerMaxSizes
FROM KarlsOrder O
   INNER JOIN (
      SELECT OId, OrderSize = Count(1)
      FROM KarlsOrderDetail D
      GROUP BY OId
   ) S ON S.OId = O.OId
GROUP BY CId

SELECT CId
FROM #CustomerMaxSizes S
   INNER JOIN (
      SELECT DISTINCT TOP 2 MaxOrderSize FROM #CustomerMaxSizes ORDER BY MaxOrderSize DESC
   ) M ON M.MaxOrderSize = S.MaxOrderSize

DROP TABLE #CustomerMaxSizes

[b]-- Method DT3: No temp tables[/b]
-- (have to repeat part of the query Common Table Expressions in Yukon will prevent this)

SELECT CId
FROM (
      SELECT CId, MaxOrderSize = Max(OrderSize)
      FROM KarlsOrder O
         INNER JOIN (
            SELECT OId, OrderSize = Count(1)
            FROM KarlsOrderDetail D
            GROUP BY OId
         ) S ON S.OId = O.OId
      GROUP BY CId
   ) T1 INNER JOIN (
      SELECT DISTINCT TOP 2 MaxOrderSize = Max(OrderSize)
      FROM KarlsOrder O
         INNER JOIN (
            SELECT OId, OrderSize = Count(1)
            FROM KarlsOrderDetail D
            GROUP BY OId
         ) S ON S.OId = O.OId
      GROUP BY CId
      ORDER BY MaxOrderSize DESC
   ) T2 ON T1.MaxOrderSize = T2.MaxOrderSize

I like DT2 the best, but in Yukon I'd use a single query by putting most of the derived tables into a common table expression.

-------------------------------------
• Every joy is beyond all others. The fruit we are eating is always the best fruit of all.
• It is waking that understands sleep and not sleep that understands waking. There is an ignorance of evil that comes from being young: there is a darker ignorance that comes from doing it, as men by sleeping lose the k

donutman · Dec 23, 2004

Where's the row counter? See the results I expect.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

ESquared · Dec 23, 2004

oops! Sorry... will fix.

-------------------------------------
• Every joy is beyond all others. The fruit we are eating is always the best fruit of all.
• It is waking that understands sleep and not sleep that understands waking. There is an ignorance of evil that comes from being young: there is a darker ignorance that comes from doing it, as men by sleeping lose the k

donutman · Dec 23, 2004

Just so that you know the row counts will be from a high of 56 to a low of 46 based on my database.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

vongrunt · Dec 27, 2004

donutman said:
If the same customer has more than one order only select the order that contains the most number of DISTINCT items ordered.

Q: if two such orders have the same number of distinct items, which one to choose?

donutman · Dec 27, 2004

Random ok. If you can do this in one query, I will be really amazed. But if it can be done in one, I'll be even more surprised if it beats a multi-step query and even the cursor approach that I need to get back to.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

chrissie1 · Dec 27, 2004

Did I give you permission to use my handle???

Christiaan Baes
Belgium

If you want to get an answer read this FAQ faq796-2540
There's no such thing as a winnable war - Sting

donutman · Dec 27, 2004

No, but I wanted it to be gender balanced and I couldn't think of any other wo-man in this forum except Chrissie1. [gorgeous]

-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

chrissie1 · Dec 27, 2004

How would you like to die????

Christiaan Baes
Belgium

If you want to get an answer read this FAQ faq796-2540
There's no such thing as a winnable war - Sting

donutman · Dec 27, 2004

I have a solution that is written for my production tables. It runs in 8 seconds and it uses vongrunt's method of row counting. The cursor version isn't going to be any faster.
I think I've figured out when cursor's degrade performance. If you use them on a recordset that's large even though you want to use only the first few rows, they put a real drain on the system performance. So when I get the temporary file size down to a manageable number, then vongrunt's method can do just fine.
Bottom line, I can't think of a problem where cursors are faster...not yet anyway.
-Karl

Code:

[Blue]DECLARE[/Blue] @Min [Blue]AS[/Blue] [Blue]int[/Blue]
[Blue]SELECT[/Blue] 
  [Blue]TOP[/Blue] 50 DT.CNKey[Gray],[/Gray] [Fuchsia]MAX[/Fuchsia][Gray]([/Gray]DT.ItemCount[Gray])[/Gray] ItemCount [Blue]INTO[/Blue] #Temp [Blue]FROM[/Blue]  
     [Gray]([/Gray][Blue]SELECT[/Blue] [Blue]TOP[/Blue] 100 [Blue]PERCENT[/Blue] CO.CNKey[Gray],[/Gray] CO.COBarCode[Gray],[/Gray] [Fuchsia]COUNT[/Fuchsia][Gray]([/Gray][Blue]DISTINCT[/Blue] ProductKey[Gray])[/Gray] ItemCount
      [Blue]FROM[/Blue] CustomerOrder CO [Blue]INNER[/Blue] [Gray]JOIN[/Gray] CustomerGeneralItem CGI
         [Blue]ON[/Blue] CO.COBarCode[Gray]=[/Gray]CGI.COBarCode
      [Blue]GROUP[/Blue] [Blue]BY[/Blue] CO.CNKey[Gray],[/Gray] CO.COBarCode
      [Blue]ORDER[/Blue] [Blue]BY[/Blue] ItemCount [Blue]DESC[/Blue][Gray])[/Gray] DT
   [Blue]GROUP[/Blue] [Blue]BY[/Blue] CNKey
   [Blue]ORDER[/Blue] [Blue]BY[/Blue] ItemCount [Blue]DESC[/Blue]
[Blue]SET[/Blue] @Min[Gray]=[/Gray][Gray]([/Gray][Blue]SELECT[/Blue] [Fuchsia]MIN[/Fuchsia][Gray]([/Gray]ItemCount[Gray])[/Gray] [Blue]FROM[/Blue] #Temp[Gray])[/Gray]
[Blue]DROP[/Blue] [Blue]TABLE[/Blue] #Temp  
[Blue]SELECT[/Blue] CO.CNKey[Gray],[/Gray] CO.COBarCode[Gray],[/Gray] [Fuchsia]COUNT[/Fuchsia][Gray]([/Gray][Blue]DISTINCT[/Blue] ProductKey[Gray])[/Gray] ItemCount
   [Blue]INTO[/Blue] #TempCO
      [Blue]FROM[/Blue] CustomerOrder CO [Blue]INNER[/Blue] [Gray]JOIN[/Gray] CustomerGeneralItem CGI
         [Blue]ON[/Blue] CO.COBarCode[Gray]=[/Gray]CGI.COBarCode
      [Blue]GROUP[/Blue] [Blue]BY[/Blue] CO.CNKey[Gray],[/Gray] CO.COBarCode
      [Blue]HAVING[/Blue] [Fuchsia]COUNT[/Fuchsia][Gray]([/Gray][Blue]DISTINCT[/Blue] ProductKey[Gray])[/Gray][Gray]>[/Gray][Gray]=[/Gray]@Min 
[Blue]SELECT[/Blue] CNKey[Gray],[/Gray] [Fuchsia]MAX[/Fuchsia][Gray]([/Gray]COBarCode[Gray])[/Gray] COBarCode[Gray],[/Gray] [Fuchsia]MAX[/Fuchsia][Gray]([/Gray]ItemCount[Gray])[/Gray] ItemCount [Blue]INTO[/Blue] #TempCnt [Blue]FROM[/Blue] #TempCO T1  
   [Blue]WHERE[/Blue] ItemCount[Gray]=[/Gray][Gray]([/Gray][Blue]SELECT[/Blue] [Fuchsia]MAX[/Fuchsia][Gray]([/Gray]ItemCount[Gray])[/Gray]
                       [Blue]FROM[/Blue] #TempCO 
                       [Blue]WHERE[/Blue] CNKey[Gray]=[/Gray]T1.CNKey[Gray])[/Gray]
   [Blue]GROUP[/Blue] [Blue]BY[/Blue] CNKey
[Blue]SELECT[/Blue] RowCnt[Gray]=[/Gray][Gray]([/Gray][Blue]SELECT[/Blue] [Fuchsia]COUNT[/Fuchsia][Gray]([/Gray][Blue]DISTINCT[/Blue] ProductKey[Gray])[/Gray] 
                  [Blue]FROM[/Blue] CustomerGeneralItem
                  [Blue]WHERE[/Blue] COBarCode[Gray]=[/Gray]CGI.COBarCode
                     [Gray]AND[/Gray] ProductKey[Gray]<[/Gray][Gray]=[/Gray]CGI.ProductKey[Gray])[/Gray][Gray],[/Gray] ItemCount[Gray],[/Gray] ProductKey[Gray],[/Gray] CGI.COBarCode[Gray],[/Gray] T1.CNKey
   [Blue]FROM[/Blue] #TempCnt T1 [Blue]INNER[/Blue] [Gray]JOIN[/Gray] CustomerGeneralItem CGI
      [Blue]ON[/Blue] T1.COBarCode[Gray]=[/Gray]CGI.COBarCode 
   [Blue]ORDER[/Blue] [Blue]BY[/Blue] ItemCount [Blue]DESC[/Blue][Gray],[/Gray] CGI.COBarCode[Gray],[/Gray] RowCnt
[Blue]DROP[/Blue] [Blue]TABLE[/Blue] #TempCO
[Blue]DROP[/Blue] [Blue]TABLE[/Blue] #TempCnt

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

ESquared · Dec 27, 2004

Instead of dissecting your query (which could skew my own thoughts about how to solve the problem) I need some things clarified. I think I misunderstood what you were looking for.

What if 2000 customers all have an order with the exact same number of products in it, say, one? Will the result have 2000 rows or 50 rows, randomly chosen?

You didn't give the columns you wanted... all you said was "The recordset that I want to generate is the top 50 distinct customers who have the most number of distinct items ordered within a single order." That's why my queries only had customer numbers. [taking a peek at your query] So, you want the count as well. Do you want the order number it comes from?

The row-counter method I've used before (derived table self-join on >=) won't work here because there's no need to join the detail table to itself to determine row order. Unless you're using the "row counter" phrase to mean something different? Basically, I'm a little confused. Where doe sthe row counting come into play?

donutman · Dec 27, 2004

What if 2000 customers all have an order with the exact same number of products in it, say, one? Will the result have 2000 rows or 50 rows, randomly chosen?

That group would add only 1 to the top 50.

donutman said:
With the example data, you should get these results:
[tt]
# PDesc OId CName
1 Apple Fritter 10 Chrissie1
2 Caramel Pecal Roll 10 Chrissie1
3 Chocolate Donut 10 Chrissie1
4 Custard Bismark 10 Chrissie1
1 Caramel Pecal Roll 12 ESQuared
2 Chocolate Donut 12 ESQuared
3 Custard Bismark 12 ESQuared
1 Apple Fritter 13 SQLSister
2 Caramel Pecal Roll 13 SQLSister
3 Custard Bismark 13 SQLSister[/tt]

That's basically the results I wanted. I admit that I worded the question poorly. It's not that easy to express it, but I know I should have done better. The fact that I was insisting on the final result to including possibly more than 50 customers was to make the query more difficult and give a cursor a further edge. The special considerations and the above resultset where supposed to make it clear. The fact that you misunderstood, is proof enough for me that I wasn't clear.
I wasn't really too concerned about the resultset, but I definitely wanted a row counter in the result. I returned the Max(RowCount) so that I could confirm the results and sort them properly. I thought for sure that the cursor was going to be able to provide a faster approach and I still believe it would have if it wasn't for the fact that a large recordset seems to cause the problem. If it hadn't I could have sorted the large recordset so that the needed rows appeared first.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

donutman · Dec 27, 2004

Oh, I didn't see your response Chrissie1. The answer is old age. Are you threatening me?
[cannon]

-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

donutman · Dec 27, 2004

Oh, no! I misread your question. 2000 is correct answer, if the count of 1 was within the original list of the top 50 customers.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

chrissie1 · Dec 27, 2004

Good answer.

A threat is only a threat when the person recieving the threat excepts it as a threat otherwise it's not a threat.

Christiaan Baes
Belgium

If you want to get an answer read this FAQ faq796-2540
There's no such thing as a winnable war - Sting

kavin · Dec 28, 2004

I Hope this will solve ur problem: Do some kind of changes as u like.. I cud be done in One query but for performance wise better to use multiple with temp tables.

For Generating serial number U have to use Temp tables OR Cursors, But Yukon (SQL 2005) have such fascility I suppose.
Happy Programming

CREATE TABLE #FINAL_RESULT
(
slno int,
PDesc varchar(100),
OID int,
CName Varchar(100)
)

SELECT KarlsOrder.OID , CID , MaxOrderSize = Max(OrderSize) INTO #ORDER
FROM KarlsOrder
INNER JOIN
( SELECT OID , Count(distinct PID) AS ORDERSIZE
FROM KarlsOrderDetail GROUP BY OID
) DETAILS
ON KarlsOrder.OID = DETAILS.OID
GROUP BY KarlsOrder.OID , KarlsOrder.CID

INSERT INTO #FINAL_RESULT ( PDesc, OID, CName )
SELECT KarlsProduct.PDesc , FINAL.OID , KarlsCustomer.CName
FROM KarlsProduct
INNER JOIN KarlsOrderDetail
ON KarlsProduct.PID = KarlsOrderDetail.PID
INNER JOIN
(
Select OID , CID from #ORDER
Where convert(varchar,#ORDER.CID) + convert(varchar,#ORDER.MaxOrderSize)
IN (
SELECT convert(varchar,ORDER1.CID) + convert(varchar,max(ORDER1.MaxOrderSize) )
FROM #ORDER AS ORDER1
INNER JOIN
(
SELECT DISTINCT TOP 2 MaxOrderSize = Max(MaxOrderSize) FROM #ORDER
GROUP BY CID Order by MaxOrderSize desc
) ORDER2
ON ORDER1.MaxOrderSize = ORDER2.MaxOrderSize
GROUP BY ORDER1.CID
)
) FINAL
ON
KarlsOrderDetail.OID = FINAL.OID
INNER JOIN KarlsCustomer
ON FINAL.CID = KarlsCustomer.CID

Update FINAL
SET slno = (SELECT COUNT(*) FROM #FINAL_RESULT
Where #FINAL_RESULT.OID = FINAL.OID
AND #FINAL_RESULT.PDesc <= FINAL.PDesc )
FROM #FINAL_RESULT FINAL

SELECT * FROM #FINAL_RESULT

Drop table #ORDER
DRop Table #FINAL_RESULT

vongrunt · Dec 28, 2004

Good work... my only objection is about convert(varchar()) stuff. It is used exclusively to make IN() work (could be this replaced with EXISTS?). Plus CId+MaxOrderSize aren't unique enough (12 + 3 becomes 123, same as 1 + 23).

kavin · Dec 28, 2004

I think using EXISTS in such scenario will not be helpful but U can make the 12 + 3 to like 12AB + 3 kind as it is varchar..so can u lots of permutation combination.

ESquared · Dec 28, 2004

Converting things to strings is, in my experience, one of the best ways to slow things wayyyyy down.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Row Count Challenge

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Similar threads

Log in

Part and Inventory Search

Sponsor