Row number of record through query ?

asp_crazy_guy · #1 (**permalink**) 02-24-10, 04:12

Hello guys,

select * from tableB b where b.RID=3 and b.ID=1;

How can we find the record number of the record in tableb like if there are 3 records with ID 1 in tableB and I select 1 record with a select statement.

How can I find which record number is this with respect to the corresponding ID, in this case it would be 3

tableA
---------
ID Description
1 First layer
2 Second layer
3 Third layer

tableB
----------
RID ID Description
1 1 abc
2 1 xyz
3 1 ------
4 2 null
4 2 abc

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

I'm not sure that I understood fully your requirements.

Here is an example...

Code:

------------------------------ Commands Entered ------------------------------
WITH
 tableA(ID , Description) AS (
VALUES
  (1 , 'First layer')
, (2 , 'Second layer')
, (3 , 'Third layer')
)
,tableB(RID , ID , Description) AS (
VALUES
  (1 , 1 , 'abc')
, (2 , 1 , 'xyz')
, (3 , 1 , '------')
, (4 , 2 , CAST(null AS VARCHAR(1)))
, (4 , 2 , 'abc')
)
SELECT b.*
     , (SELECT COUNT(*)
          FROM tableB c
         WHERE c.ID = b.ID
       ) AS "number of rows with same ID"
     , a.Description AS a_Description
  FROM tableB b
  JOIN tableA a
   ON  a.ID  = b.ID
 WHERE b.RID = 3
   AND b.ID  = 1
;
------------------------------------------------------------------------------

RID         ID          DESCRIPTION number of rows with same ID A_DESCRIPTION
----------- ----------- ----------- --------------------------- -------------
          3           1 ------                                3 First layer  

  1 record(s) selected.

This is another example using OLAP specification.
It may give you better performance.

Code:

SELECT b.*
     , a.Description AS a_Description
  FROM (SELECT b.*
             , COUNT(*) OVER()
                 AS "number of rows with same ID"
          FROM tableB b
         WHERE b.ID  = 1
       ) b
  JOIN tableA a
   ON  a.ID  = b.ID
 WHERE b.RID = 3
;
------------------------------------------------------------------------------

RID         ID          DESCRIPTION number of rows with same ID A_DESCRIPTION
----------- ----------- ----------- --------------------------- -------------
          3           1 ------                                3 First layer  

  1 record(s) selected.

asp_crazy_guy · #3 (**permalink**) 02-25-10, 03:19

Hi good man Tonkuma, hope you are good

What I meant was to find the row number with respect to the key in the child table.

For example

There are 3 rows in the child table each with ID 1 and RID 24,25,26

Now if I select the row with ID =1 and RID =25 I want to get the serial No./record no. with respect to the ID , that will be 2 in this case.

Thanks for your help as always

Regards, Mike.

Quote:

Originally Posted by tonkuma

I'm not sure that I understood fully your requirements.

Here is an example...

Code:

------------------------------ Commands Entered ------------------------------
WITH
 tableA(ID , Description) AS (
VALUES
  (1 , 'First layer')
, (2 , 'Second layer')
, (3 , 'Third layer')
)
,tableB(RID , ID , Description) AS (
VALUES
  (1 , 1 , 'abc')
, (2 , 1 , 'xyz')
, (3 , 1 , '------')
, (4 , 2 , CAST(null AS VARCHAR(1)))
, (4 , 2 , 'abc')
)
SELECT b.*
     , (SELECT COUNT(*)
          FROM tableB c
         WHERE c.ID = b.ID
       ) AS "number of rows with same ID"
     , a.Description AS a_Description
  FROM tableB b
  JOIN tableA a
   ON  a.ID  = b.ID
 WHERE b.RID = 3
   AND b.ID  = 1
;
------------------------------------------------------------------------------

RID         ID          DESCRIPTION number of rows with same ID A_DESCRIPTION
----------- ----------- ----------- --------------------------- -------------
          3           1 ------                                3 First layer  

  1 record(s) selected.

This is another example using OLAP specification.
It may give you better performance.

Code:

SELECT b.*
     , a.Description AS a_Description
  FROM (SELECT b.*
             , COUNT(*) OVER()
                 AS "number of rows with same ID"
          FROM tableB b
         WHERE b.ID  = 1
       ) b
  JOIN tableA a
   ON  a.ID  = b.ID
 WHERE b.RID = 3
;
------------------------------------------------------------------------------

RID         ID          DESCRIPTION number of rows with same ID A_DESCRIPTION
----------- ----------- ----------- --------------------------- -------------
          3           1 ------                                3 First layer  

  1 record(s) selected.

sathyaram_s · #4 (**permalink**) 02-25-10, 03:58

there is no concept of order when storing data in a relational table ... you have to define, how you order it ...

Code:

select id,rid,description,rownumber() over (partition by id order by rid) as rownum from tableb

or

Code:

select id,rid,description,rownumber() over ( order by rid) as rownum from tableb
where id=1

tonkuma · #5 (**permalink**) 02-25-10, 05:14

Quote:

What I meant was to find the row number with respect to the key in the child table.

For example

There are 3 rows in the child table each with ID 1 and RID 24,25,26

Now if I select the row with ID =1 and RID =25 I want to get the serial No./record no. with respect to the ID , that will be 2 in this case.

It's easy to get the result by modifying a little my previous example, like this...

Code:

 ------------------------------ Commands Entered ------------------------------
WITH
 tableA(ID , Description) AS (
VALUES
  (1 , 'First layer')
, (2 , 'Second layer')
, (3 , 'Third layer')
)
,tableB(RID , ID , Description) AS (
VALUES
  (24 , 1 , 'abc')
, (25 , 1 , 'xyz')
, (26 , 1 , '------')
, ( 4 , 2 , CAST(null AS VARCHAR(1)))
, ( 5 , 2 , 'abc')
)
SELECT b.*
     , a.Description AS a_Description
  FROM (SELECT b.*
             , ROW_NUMBER() OVER(ORDER BY rid)
                 AS "row number within same ID"
          FROM tableB b
         WHERE b.ID  = 1
       ) b
  JOIN tableA a
   ON  a.ID  = b.ID
 WHERE b.RID = 25
;
------------------------------------------------------------------------------

RID         ID          DESCRIPTION row number within same ID A_DESCRIPTION
----------- ----------- ----------- ------------------------- -------------
         25           1 xyz                                 2 First layer  

  1 record(s) selected.

asp_crazy_guy · #6 (**permalink**) 02-26-10, 00:48

Thanks Tonkuma, the catch was using the subquery.
You are helpful and amazing as always

Best regards, Mike.

Internet Services

Web Development

Digital Marketing

Consumer Tech