Assinging a "rank" to a record

Hi all,

I don't know how to explain what I wanna do in english so here's a concrete example:

I have this data
Item - Qty
IT1 - 2
IT2 - 2
IT1 - 4
IT1 - 5
IT2 - 2

And I wanna do something like this
Item - Rank - Qty
IT1 - 1 - 2
IT1 - 2 - 4
IT1 - 3 - 5
IT2 - 1 - 2
IT2 - 2 - 2

So basically I want to assing a rank (on the fly) in a SELECT statement

Thanks in advance

Or Thowhat version of sql server?|||MS SQL SERVER 2005 SE SP2
(9.00.3054.00 SP2 Standard Edition)|||I found the way to do it:
RANK() OVER(PARTITION BY l.RealMP ORDER BY l.MP ASC, l.Color ASC, l.Thick DESC) AS RNK
But now I have an other plroblem, I need to use it in a ON statement like this:
LEFT JOIN ##tmp ON RANK() OVER(PARTITION BY l.RealMP ORDER BY l.MP ASC, l.Color ASC, l.Thick DESC)=1

But when I try to do that I get this error:
Msg 4108, Level 15, State 1, Procedure sp_RptProdLeatherPlanning, Line 35
Windowed functions can only appear in the SELECT or ORDER BY clauses.|||please show the real layouts of your tables

i don't recall seeing those column names in your first post

and what's with the join? do you just want the row with the min value?|||Actually the data comes from more than one tables

Here's my Query:

SELECT l.MP, LOWER(l.Color) AS Color, l.Thick, '' AS [ùSTOCKFCù], RANK() OVER(PARTITION BY l.RealMP ORDER BY l.MP ASC, l.Color ASC, l.Thick DESC) AS RNK, t.*, '' AS [ùSTOCKLCù], d.DEP,
COALESCE(s.OnHand,0) AS OnHand, COALESCE(s.Alloc,0) AS Alloc, COALESCE(s.BO,0) AS BO, COALESCE(s.Dispo,0) AS Dispo, COALESCE(s.Btq,0) AS Btq,
COALESCE(p.QTY,0) AS Prod, COALESCE(mb.MinBtq,0) AS MinBtq, COALESCE(sa.QTY,0) AS SalesASAT, COALESCE(s12.QTY/12,0) AS SalesLast12M,
COALESCE(ca.QTY,0) AS ConsoASAT, COALESCE(c12.QTY/12,0) AS ConsoLast12M
FROM ##ut_RptProdLeatherPlanningLeathers l
INNER JOIN ##ut_RptProdLeatherPlanningThicksPvt t ON l.RealMP=t.MP
LEFT JOIN ##ut_RptProdLeatherPlanningDEP d ON d.MP=l.RealMP
LEFT JOIN ##ut_RptProdLeatherPlanningStocks s ON s.RealMP=l.RealMP AND d.DEP=s.DEP
LEFT JOIN ##ut_RptProdLeatherPlanningProd p ON p.RealMP=l.RealMP AND p.DEP=d.DEP
LEFT JOIN ##ut_RptProdLeatherPlanningMinBtq mb ON mb.RealMP=l.RealMP AND mb.DEP=d.DEP
LEFT JOIN ##ut_RptProdLeatherPlanningSalesAsat sa ON sa.RealMP=l.RealMP AND sa.DEP=d.DEP
LEFT JOIN ##ut_RptProdLeatherPlanningSalesL12M s12 ON s12.RealMP=l.RealMP AND s12.DEP=d.DEP
LEFT JOIN ##ut_RptProdLeatherPlanningConsoASAT ca ON ca.RealMP=l.RealMP AND ca.DEP=d.DEP
LEFT JOIN ##ut_RptProdLeatherPlanningConsoL12M c12 ON c12.RealMP=l.RealMP AND c12.DEP=d.DEP
WHERE d.DEP IS NOT NULL
ORDER BY l.MP, l.Color, l.Thick DESC, d.DEP ASC

I want to use it to join ##ut_RptProdLeatherPlanningThicksPvt to ##ut_RptProdLeatherPlanningLeathers
Where RANK() OVER(PARTITION BY l.RealMP ORDER BY l.MP ASC, l.Color ASC, l.Thick DESC) is equal to 1

So... which table layout do you want me to show you?|||wow

just wow

my question is, how do you reconcile that query with the data you posted initially...

I have this data
Item - Qty
IT1 - 2
IT2 - 2
IT1 - 4
IT1 - 5
IT2 - 2

just curious, but are those all temp tables?

and what are you really trying to do with the rank number?|||I just wanted to simplify it to you...

What I wanna do with the rank is a bit tricky.
I have this MP, Color, Thick, STOCK .5, STOCK .7, STOCK .9, STOCK...

And the real data looks like this:

MP - Color - Thick - Stock.5 - Stock.7 - ...
aniline - black - 0.7 - NULL - 100.52
aniline - black - 0.7 - NULL - 100.52
aniline - black - 0.5 - 10 - NULL
aniline - black - 0.5 - 10 - NULL
soft - brown - 0.7 - NULL - 3039.42
soft - brown - 0.5 - 2039.42 - NULL
soft - brown - 0.5 - 2039.42 - NULL

And this is what I want.
MP - Color - Thick - Stock.5 - Stock.7 - ...
aniline - black - 0.7 - NULL - 100.52
aniline - black - 0.7 - NULL - NULL
aniline - black - 0.5 - 10 - NULL
aniline - black - 0.5 - NULL - NULL
soft - brown - 0.7 - NULL - 3039.42
soft - brown - 0.5 - 2039.42 - NULL
soft - brown - 0.5 - NULL - NULL

So if I add my rank it will looks like this
MP - Color - Thick - Rank - Stock.5 - Stock.7 - ...
aniline - black - 0.7 - 1 - NULL - 100.52
aniline - black - 0.7 - 2 - NULL - NULL
aniline - black - 0.5 - 1 - 10 - NULL
aniline - black - 0.5 - 2 -NULL - NULL
soft - brown - 0.7 - 1 - NULL - 3039.42
soft - brown - 0.5 - 1 - 2039.42 - NULL
soft - brown - 0.5 - 2 - NULL - NULL|||just curious, but are those all temp tables?

Exact I'm doing this before the select, to fill my temp tables

EXEC sp_RptProdLeatherPlanningLeathers
IF EXISTS(SELECT name FROM tempdb.dbo.sysobjects WHERE type='U' AND name = '##ut_RptProdLeatherPlanningThicksPvt')
DROP TABLE ##ut_RptProdLeatherPlanningThicksPvt
EXEC sp_CrossTabIntoTable
@.select = 'SELECT MP, CAST(Thick AS varchar) AS Thick FROM ##ut_RptProdLeatherPlanningThicks GROUP BY MP, Thick',
@.sumfunc = 'SUM(PvtSum)',
@.pivot = 'Thick',
@.table = '##ut_RptProdLeatherPlanningThicks',
@.tbl_result = '##ut_RptProdLeatherPlanningThicksPvt',
@.fld_sufx = 'stk de'
EXEC sp_RptProdLeatherPlanningBOM
EXEC sp_RptProdLeatherPlanningStocks
EXEC sp_RptProdLeatherPlanningDEP
EXEC sp_RptProdLeatherPlanningProd
EXEC sp_RptProdLeatherPlanningMinBtq
EXEC sp_RptProdLeatherPlanningSales
EXEC sp_RptProdLeatherPlanningConso|||and what's with the join? do you just want the row with the min value?

No! I want all of the rows but I want no data on STOCK.* WHERE RANK>1 because the data is repeated ...|||I know this is not the best but I gonna SELECT to an OUTPUT table and do an UPDATE to have the result I need.

If someone finds a better way to do it... please tell me.

Regards

Or Tho