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/SQL Database question

Is there anyway of putting a prefix on an primary key field? I'll try explain with an example.

tblCodes

CodeID [PK] - Integer
CodeName
CodeDesc
CodeType

When a new code is created the ID is simply the next value as you would expect. To help with identifying the codes in my actual application, I would like the ID to be based on the CodeType.

For example: There are four types of code (red, green, blue, orange), if when creating a new code the user selects the type red, the CodeID will be "RED\1". If another is made using the type red, it will become "RED\2". The same applied the the others, a green code will have a prefix of "GREEN\" which increments.

Really not sure how to go about doing this, maybe a seperate table for CodeTypes is needed? I'm a novice programmer and i'm also new to SQL to please to be gentle!

There is and only needs to be 4 code types, if that's any help.

To give a bit more information on the reasoning for wanting the prefix on the CodeID.

Scenario
A user is inputting the amount of time he has spent on a code. There is a drop down value which he must select the CodeID from. At the moment there is no distinguishing between the CodeTypes, so he will just see 1, 2, 3 ,4 ,5.

If I can do what i'm wanting, the user will see GREEN\1, GREEN\2, RED\1, RED\2 and so on. Making it a fair bit more user friendly.

Any ideas?

I would suggest using a separate field to store the CodeType (Red, Green etc) in addition to the ID.

This will give you the flexibility of using it in different ways e.g. concatenate in sql statement to generate strings like "Green/1" or use it separately to say group by CodeType.

|||

Ideally, you need to create another table to store user/CodeType pair to deal with Many-To-Many relationship. The table at least includes two columns: UserID and CodeId.

|||

I don't really see why you are saying I need to have UserID in there at all.

I'm still not sure how to do this :(

It's only really needed for presentation reasons. So the actual value doesn't need to be stored as GREEN\1.

For example, I want the drop down menu to show GREEN\1 or RED\12 simply to make it easier for the user to tell what time of code his is picking, rather then all the codes looking the same but just with different numbers (1, 2, 3, 4, 5, 6).

Any ideas?

ASP.NET SQL Server Transactions

Hi,
I am currently having dificulties in finding a way to use transactions while using classes for table representation.
Let me explain, i have two tables a Customers and a Movements one, so two tables = two classes. Each class supports Insert, Edit and Delete, so if this tables worked seperatly there would be no problem but in this case whenever i create a customer i must create a movement for that customer so the connection don't pass between classes and i cannot use transactions ;(
Is there any brilliant way to use transactions any other way? Even between connections or getting a solution for my implementation?
Best Regards,
Luis Sim?esRun a search for Cascade Delete and Cascade Update and DRI(declarative referenctial integrity) Triggers in SQL Server BOL(books online). Hope this helps.