Geek Answers Handbook

Can I get the record position in the SQL result table?

If I'm something like

SELECT * FROM mytable ORDER BY mycolumn ASC;

I get a table of results in a specific order.

Is there a way in SQL to effectively find out, taking into account PK, what position in this table of results will the record with my PK contain?

+5
source share
5 answers

In databases that support it, you can use ROW_NUMBER () for this purpose:

SELECT RowNr
FROM (
    SELECT 
         ROW_NUMBER() OVER (ORDER BY mycolumn) AS RowNr,
         mycolumn
    FROM mytable
) sub
WHERE sub.mycolumn = 42

The example assumes that you are looking for primary key 42 :)

A subquery is needed because something like:

SELECT 
     ROW_NUMBER() OVER (ORDER BY mycolumn) AS RowNr
FROM mytable
WHERE sub.mycolumn = 42

Will always return 1; ROW_NUMBER () works after WHERE, so to speak.

+17
source

, , , , , :

select count(*)
from mytable
where mycolumn < (select mycolumn from mytable where key = 42)
+16

SQL . , , " " .

, ResultSet .

+2

, " ".

, , ORDER BY ROW_NUMBER ( ), - .

This position does not map back to any position in the table, although ORDER BY is in the set of clustered index columns, but even then this position may be invalid the next second.

What I would like to know is what you intended to use for this “position”.

0
source

This is not to say that without selecting the entire subset of records. If your PC is an integer type, you can

select count(*) from mytable 
    where id <= 10 -- Record with ID 10
    order by mycolumn asc
-1
source

More articles:


All Articles