CODEWITHSUNDEEP
sqlsql-server
Gold
Gold

Reputation: 62434

How to find row after query?

I have a query, and I need to find the row number that the query return the answer. I do not have a counter field. How do I do it?

Thanks in advance.

Upvotes: 0

Views: 173

Answers (4)

kristof
kristof

Reputation: 53824

I have a Query, and I need to find the row number that the query return the answer. I do not have any counter field. how I do it ?

If I understand your question correctly, you have some sort of query that returns a row, or a few rows (answer, answers) and you want to have a row number associates with an "answer"

As you said you do not have any counter field so what you need to to is the following:

  • decide on the ordering criteria
  • add the counter
  • run the query

It would really help if you provided more details to your question(s). If you confirm that I understand your question correctly, I will add a code example

Upvotes: 0

rmdussa
rmdussa

Reputation: 1557

create table t1 (N1 int ,N2 int)

insert into t1 select 200,300

insert into t1 select 200,300

insert into t1 select 300,400

insert into t1 select 400,400 ..... ......

select row_number() over (order by [N1]) RowNumber,* from t1

Upvotes: 0

John Gietzen
John Gietzen

Reputation: 49544

If you have a primary key, you can use this method on SQL Server 2005 and up:

SELECT 
  ROW_NUMBER() OVER (ORDER BY PrimaryKeyField) as RowNumber,
  Field1,
  Field2
FROM
  YourSourceTable

If you don't have a primary key, what you may have to do is duplicate your table into a mermory table (or a temp table if it is very large) using a method like this:

DECLARE @NewTable table (
    RowNumber BIGINT IDENTITY(1,1) PRIMARY KEY,
    Field1 varchar(50),
    Field2 varchar(50),
)

INSERT INTO @NewTable
    (Field1, Field2)
SELECT
    Field1,
    Field2
FROM
    YourSourceTable

SELECT
    RowNumber,
    Field1,
    Field2
FROM
    @NewTable

The nice part about this, is that you will be able to detect identical rows if your source table does not have a primary key.

Also, at this point, I would suggest adding a primary key to every table you have, if they don't already have one.

Upvotes: 0

Drahakar
Drahakar

Reputation: 6078

SELECT 
  ROW_NUMBER() OVER (<your_id_field_here> ORDER BY <field_here>) as RowNum,
  <the_rest_of_your_fields_here>
FROM
  <my_table>

Upvotes: 2

Related Questions