Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2009
    Posts
    73

    Red face Unanswered: Select row_number of table wrt id.

    Hi I have to select serial of records (Layer No) from a table w.r.t. the ID of the reocords like for a sample table I need results like

    Layer No | ID
    1   4
    2   4
    3   4
    4   4
    1   5
    2   5
    3   5
    4   5

    I tried row_number but its giving an error.


    select a.GENLAYERSSRNO,(select row_number() over (ORDER BY b.GENFORTREATYSRNO)as LAYERNUM from administrator.uwftrtylayer as b where a.genfortreatysrno=b.genfortreatysrno) , a.GENFORTREATYSRNO, a.GENMINAMT, a.GENMAXAMT, a.GENPREMMDP, a.GENMDPRATE, a.GENNOOFLOSSRECOV
    from administrator.uwftrtylayer as a

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Would you give me a sample data of administrator.uwftrtylayer?
    Especialy values of columns GENLAYERSSRNO and genfortreatysrno and resulting values of Layer No and ID.

  3. #3
    Join Date
    Mar 2009
    Posts
    73

    Wink hi

    Hi thanks for the reply,

    Its like
    Code:
    GENLAYERSSRNO      GENFORTREATYSRNO
                        333                                15
                        341                                15
                        245                                10
                        210                                10
                        211                                11
    And I want to select like

    Code:
    LayerNum   GENLAYERSSRNO   GENFORTREATYSRNO
                  1                   333                          15
                  2                   341                          15
                  1                   210                          10 
                  2                   245                          10
                  1                   211                          11

    Quote Originally Posted by tonkuma
    Would you give me a sample data of administrator.uwftrtylayer?
    Especialy values of columns GENLAYERSSRNO and genfortreatysrno and resulting values of Layer No and ID.

    Sorry about the columns going crazy, I cant figure how to allign them, basically its the first column going into the second column due to spacing.
    Last edited by asp_crazy_guy; 07-24-09 at 08:45.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    PARTITON BY would be a trick.
    Code:
    SELECT ROW_NUMBER()
             OVER(PARTITION BY GENFORTREATYSRNO
                  ORDER BY GENLAYERSSRNO) AS LayerNum
         ,GENLAYERSSRNO, GENFORTREATYSRNO
      FROM administrator.uwftrtylayer

  5. #5
    Join Date
    Mar 2009
    Posts
    73

    Thumbs up

    Thanks for the reply mister tonkuma !

    I tried this

    select a.GENLAYERSSRNO,(select count(*) from administrator.uwftrtylayer as b where b.genfortreatysrno=a.genfortreatysrno AND b.GENLAYERSSRNO<=a.GENLAYERSSRNO)as LAYERNUM , a.GENFORTREATYSRNO, a.GENMINAMT, a.GENMAXAMT, a.GENPREMMDP, a.GENMDPRATE, a.GENNOOFLOSSRECOV
    from administrator.uwftrtylayer as a ORDER BY a.GENFORTREATYSRNO,a.GENLAYERSSRNO

    Which is the better approach mine or yours ? Also there is not much on partition by clause on google

    Thanks once again

    Quote Originally Posted by tonkuma
    PARTITON BY would be a trick.
    Code:
    SELECT ROW_NUMBER()
             OVER(PARTITION BY GENFORTREATYSRNO
                  ORDER BY GENLAYERSSRNO) AS LayerNum
         ,GENLAYERSSRNO, GENFORTREATYSRNO
      FROM administrator.uwftrtylayer

  6. #6
    Join Date
    Mar 2009
    Posts
    73
    Hello, Mr. tonkuma ,

    Your query seems to gives incremental row numbers on the same row when used with a select of combination of two tables like

    Code:
     SELECT ROW_NUMBER()
             OVER(PARTITION BY a.GENFORTREATYSRNO
                  ORDER BY GENLAYERSSRNO ASC) AS LayerNum
         ,a.GENLAYERSSRNO, a.GENFORTREATYSRNO, a.GENMINAMT, a.GENMAXAMT, a.GENPREMMDP, a.GENMDPRATE, a.GENNOOFLOSSRECOV,
    	b.gengnpisrno 
      FROM administrator.uwftrtylayer a,administrator.uwftrtygnpi b  where a.genfortreatysrno=b.genfortreatysrno ORDER BY a.GENFORTREATYSRNO,a.GENLAYERSSRNO  ;
    Would'nt it give results like when used with a multitable select ?

    Like for two tables

    Layertable { LID, LayerNo } . GNPITable {LID, GNPINO}
    ------------ ----------------------
    1 1 1 1
    1 2 1 2
    1 3 1 3

    Layer no **GNPI No LID

    1 1 1
    1 2 1
    1 3 1
    2 1 1
    2 2 1
    2 3 1
    3 1 1
    3 2 1
    3 3 1

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Which is the better approach mine or yours ?
    My opinion is OLAP expressions have some advantages:
    1) More straightforward expression, if you know well OLAP expressions.
    2) Shorter expression.
    3) Many times better performance from my experiences.

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    When used with a combination of two tables,
    you can take one of ROW_NUM, LAYERNO_RANK or GNPINO_RANK in the following example, depending on your requirement.
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH Layertable(LID, LayerNo) AS (
    VALUES
     (1, 1)
    ,(1, 3)
    ,(1, 7)
    )
    ,GNPITable(LID, GNPINo) AS (
    VALUES
     (1, 2)
    ,(1, 6)
    ,(1, 8)
    )
    SELECT LayerNo
         , GNPINo
         , a.lid
         , ROW_NUMBER()
             OVER(PARTITION BY a.lid
                  ORDER BY LayerNo, GNPINo) AS row_num
         , DENSE_RANK()
             OVER(PARTITION BY a.lid
                  ORDER BY LayerNo) AS LayerNo_rank
         , DENSE_RANK()
             OVER(PARTITION BY a.lid
                  ORDER BY GNPINo) AS GNPINo_rank
      FROM Layertable a
         , GNPITable  b
     WHERE a.lid = b.lid
     ORDER BY
           a.lid
         , LayerNo
         , GNPINo
    ;
    ------------------------------------------------------------------------------
    
    LAYERNO     GNPINO      LID         ROW_NUM              LAYERNO_RANK         GNPINO_RANK         
    ----------- ----------- ----------- -------------------- -------------------- --------------------
              1           2           1                    1                    1                    1
              1           6           1                    2                    1                    2
              1           8           1                    3                    1                    3
              3           2           1                    4                    2                    1
              3           6           1                    5                    2                    2
              3           8           1                    6                    2                    3
              7           2           1                    7                    3                    1
              7           6           1                    8                    3                    2
              7           8           1                    9                    3                    3
    
      9 record(s) selected.

  9. #9
    Join Date
    Mar 2009
    Posts
    73
    Thanks your SQL skills are amazing as always, I never knew about all these functions. Your query is much more simple and straightforward then the one I used.

Posting Permissions

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