Results 1 to 11 of 11
  1. #1
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172

    Question Unanswered: 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 Tho
    Less is more.
    How long is now?
    http://www.lesouterrain.com

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what version of sql server?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172
    MS SQL SERVER 2005 SE SP2
    (9.00.3054.00 SP2 Standard Edition)
    Less is more.
    How long is now?
    http://www.lesouterrain.com

  4. #4
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172
    I found the way to do it:
    Code:
    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:
    Code:
     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.
    Less is more.
    How long is now?
    http://www.lesouterrain.com

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172
    Actually the data comes from more than one tables

    Here's my Query:
    Code:
    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?
    Last edited by ortho; 11-23-07 at 14:38.
    Less is more.
    How long is now?
    http://www.lesouterrain.com

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172
    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
    Less is more.
    How long is now?
    http://www.lesouterrain.com

  9. #9
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172
    Quote Originally Posted by r937
    just curious, but are those all temp tables?
    Exact I'm doing this before the select, to fill my temp tables

    Code:
    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
    Less is more.
    How long is now?
    http://www.lesouterrain.com

  10. #10
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172
    Quote Originally Posted by r937
    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 ...
    Less is more.
    How long is now?
    http://www.lesouterrain.com

  11. #11
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172
    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
    Last edited by ortho; 11-23-07 at 17:00.
    Less is more.
    How long is now?
    http://www.lesouterrain.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •