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

    Cool Unanswered: Row number of record through query ?

    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

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.
    Last edited by tonkuma; 02-24-10 at 10:03. Reason: Remove "PARTITION BY id" from second example.

  3. #3
    Join Date
    Mar 2009
    Posts
    73
    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 View Post
    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.

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.

  6. #6
    Join Date
    Mar 2009
    Posts
    73

    Thumbs up

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

    Best regards, Mike.

Posting Permissions

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