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

Asp.net / sql help

ok first satrt with asp .net as this is simple or should be

< %# DataBinder.Eval(Container.DataItem, "Price", "{0:c}") %>
the {0:c} defines it to 2 decimal palces and i assume as a currency

now i have a short date one but it displays the time as well obviousley time is just 00:00:00 but was is the mask for it?

< %# DataBinder.Eval(Container.DataItem, "Date", "?mask??") %
ok now for sql i have 3 tables as follow
[code]

[SIZE=3]Education[/SIZE]
edId - primary key
edSchool
edDateBegin
edDateEnd

[SIZE=3]EducationLevel[/SIZE]
edId – foreign key link
lvlId – primary key
Level

[SIZE=3]EducationMarks[/SIZE]
lvlId – foreign key link
lvlname
Description
[/code]

And at the minute have got it working like this
[code]
ALTER PROCEDURE GetMarks

AS

SELECT edSchool, edDatebegin, edDateEnd,
EducationLevel.level
FROM Education INNER JOIN EducationLevel
ON Education.edID = EducationLevel.edID

RETURN
[/code]

How do i include the third table in it?
i tried this but syntax errors on the 2nd on statment

[code]

ALTER PROCEDURE GetMarks

AS

SELECT edSchool, edDatebegin, edDateEnd,
EducationLevel.level, EducationMark.lvlName, EducationMark.Description
FROM Education INNER JOIN EducationLevel INNER JOIN EducationMark
ON Education.edID = EducationLevel.edID and EducationLevel.lvlid = EducationMark.lvlId

RETURN

[/code]

Any help Much apreciated.Try

ALTER PROCEDURE GetMarks
AS
SELECT edSchool, edDatebegin, edDateEnd,
EducationLevel.level, EducationMark.lvlName, EducationMark.Description
FROM Education INNER JOIN EducationLevel ON Education.edID = EducationLevel.edID INNER JOIN EducationMark on EducationLevel.lvlid = EducationMark.lvlId
RETURN

|||thx got that working i just cant figure out how to get it. i need a list in a list how the hell do you do this i cant get my head round it.

i have 3 tables as above the middle one just being a joining table so you dont have many to many relationships. now i want to display it as below

Title1
content 1
content 2
content 3

Title 2
blah 1
blah 2
blah 3

Title 4
ex 1
ex 2
ex 3

Ect ect

But im using a datalist template item but that only does 1 list not a list inside a list so i get the following

Title1 - content 1
Title1 - content 2
Title1 - content 3
Title1 - content 4

Title 2 - blah 1
Title 2 - blah 2
Title 2 - blah 3

Ect ect can any1 help or give me a link which expalins how to do this thanx