Select row_number of table wrt id.

asp_crazy_guy · #1 (**permalink**) 07-24-09, 07:01

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

tonkuma · #2 (**permalink**) 07-24-09, 07:26

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.

asp_crazy_guy · #3 (**permalink**) 07-24-09, 07:39

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.

tonkuma · #4 (**permalink**) 07-24-09, 08:12

PARTITON BY would be a trick.

Code:

SELECT ROW_NUMBER()
         OVER(PARTITION BY GENFORTREATYSRNO
              ORDER BY GENLAYERSSRNO) AS LayerNum
     ,GENLAYERSSRNO, GENFORTREATYSRNO
  FROM administrator.uwftrtylayer

asp_crazy_guy · #5 (**permalink**) 07-27-09, 04:11

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

asp_crazy_guy · #6 (**permalink**) 07-27-09, 05:47

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

tonkuma · #7 (**permalink**) 07-27-09, 08:52

Quote:

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.

tonkuma · #8 (**permalink**) 07-27-09, 09:14

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.

asp_crazy_guy · #9 (**permalink**) 07-29-09, 00:34

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech