locked
I want to get the row value of a query.... RRS feed

  • Question

  • Dear friends,

    I want to do a script where I have to get the current position of the cursor in sql db.

    sql= “Select * from Mytab where description=” &$tagsearch&“ “

    and I want to know exactly the number of the row

    with Which command can I know the actual position of the cursor?

    If I have the next table

    key        description         value

    1             A letter                                1

    2             B letter                2

    3             C letter                3

    ...


    .10          

    I type in tagsearch=”B letter”, 

    In a few words I want to get the value of the row. 

    Saturday, December 20, 2014 7:18 PM

Answers

  • Try the below: Thats bcz you are doing the ROW_NUMBER for the Selected records and you have only one value for  in the selected group.You may try the below:

    Create table centrodecostos
    (
    ID int identity(1,1) not null,
    ClaveCC int primary key,
    Descripcion nvarchar(50),
    )	
    
     insert into centrodecostos(ClaveCC,Descripcion) values ('3000','Ejemplo1')
      insert into centrodecostos(ClaveCC,Descripcion) values ('3001','Ejemplo2')
      insert into centrodecostos(ClaveCC,Descripcion) values ('3003','Ejemplo3')
      insert into centrodecostos(ClaveCC,Descripcion) values ('3004','Ejemplo4')
      insert into centrodecostos(ClaveCC,Descripcion) values ('3005','Ejemplo5')
      insert into centrodecostos(ClaveCC,Descripcion) values ('3006','Ejemplo6')
      insert into centrodecostos(ClaveCC,Descripcion) values ('3007','Ejemplo8')
      
      ;With cte as 
      (Select *, Row_Number()Over (Order by ID asc) Rn From centrodecostos)
      select * from cte where Id=7;
      
      Drop table centrodecostos

    • Marked as answer by GeorgeRoman Sunday, December 21, 2014 2:44 AM
    Sunday, December 21, 2014 2:13 AM
    Answerer

All replies

  • Sorry which cursor position are you asking about? I cant see any cursor declared in the above code

    Can you specify what would be your required output? Do you mean returning the key value of the row?


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Saturday, December 20, 2014 7:47 PM
  • First of all, Thanks for your response.

    This my table where I emerged this need

    Create table centrodecostos
    (
    ID int identity(1,1) not null,
    ClaveCC int primary key,
    Descripcion nvarchar(50),
    )  

    And insert for example these values

     

     use dbmaria;
      insert into centrodecostos(ClaveCC,Descripcion) values ('3000','Ejemplo1')
      insert into centrodecostos(ClaveCC,Descripcion) values ('3001','Ejemplo2')
      insert into centrodecostos(ClaveCC,Descripcion) values ('3003','Ejemplo3')
      insert into centrodecostos(ClaveCC,Descripcion) values ('3004','Ejemplo4')
      insert into centrodecostos(ClaveCC,Descripcion) values ('3005','Ejemplo5')
      insert into centrodecostos(ClaveCC,Descripcion) values ('3006','Ejemplo6')
      insert into centrodecostos(ClaveCC,Descripcion) values ('3007','Ejemplo8')

      If I want to make a query, but in this result i want to know in which row (int) found that register:

     

      select * from centrodecostos where Id=7;

    The result is : 

    ID CLAVECC DESCRIPTION

    7 3007 EXAMPLE 7

    But I want to get the number of the row.... 

    I try with fuction like row_number() but I always get the same result : "1". when I specify the query like ID=7

     SELECT *,ROW_NUMBER() OVER (ORDER BY ID) AS #
      FROM centrodecostos WHERE ID=7

    THE RESULT: IS  ALWAYS  ONE (1), I WANNA GET 7.


    I HOPE YOU CAN HELP ME...


    Sunday, December 21, 2014 12:04 AM
  • Try the below: Thats bcz you are doing the ROW_NUMBER for the Selected records and you have only one value for  in the selected group.You may try the below:

    Create table centrodecostos
    (
    ID int identity(1,1) not null,
    ClaveCC int primary key,
    Descripcion nvarchar(50),
    )	
    
     insert into centrodecostos(ClaveCC,Descripcion) values ('3000','Ejemplo1')
      insert into centrodecostos(ClaveCC,Descripcion) values ('3001','Ejemplo2')
      insert into centrodecostos(ClaveCC,Descripcion) values ('3003','Ejemplo3')
      insert into centrodecostos(ClaveCC,Descripcion) values ('3004','Ejemplo4')
      insert into centrodecostos(ClaveCC,Descripcion) values ('3005','Ejemplo5')
      insert into centrodecostos(ClaveCC,Descripcion) values ('3006','Ejemplo6')
      insert into centrodecostos(ClaveCC,Descripcion) values ('3007','Ejemplo8')
      
      ;With cte as 
      (Select *, Row_Number()Over (Order by ID asc) Rn From centrodecostos)
      select * from cte where Id=7;
      
      Drop table centrodecostos

    • Marked as answer by GeorgeRoman Sunday, December 21, 2014 2:44 AM
    Sunday, December 21, 2014 2:13 AM
    Answerer