How to Determine Position of Row in SQL Result-Set

How to determine position of row in sql result-set?

The previous posts are correct. Use ROW_NUMBER if using Microsoft SQL Server 2005 or greater.

However, your tags do not specify that you're using MSSQL, so here's a solution that should work across most RDBMS implementations. Essentially, use a correlated subquery to determine the count of rows in the same set that are less than the current row, based on the values of the ORDER clause of the outer query. Something like this:

SELECT      T1.id,
            T1.[name],
            (SELECT COUNT(*) 
             FROM table T2 
             WHERE T2.[name] < T1.[name]) + 1 AS rowpos
FROM        table T1
ORDER BY    T1.[name]

sql - get row position as a result

You can do what you want in one query (which the referenced duplicate question does not do):

SELECT (@rn := @rn + 1) as rownumber, c.*
FROM computers c cross join
     (select @rn := 0) var
WHERE account_id = :account_id
ORDER BY computer_id;

To get a row number across all rows, use a subquery:

SELECT c.*
FROM (SELECT (@rn := @rn + 1) as rownumber, c.*
      FROM computers c cross join
           (select @rn := 0) var
      ORDER BY computer_id
     ) c
WHERE account_id = :account_id;

Note that this will take more time, because it has to process the entire table.

Postgres: Find position of a specific row within a resultset?

Use analytic/ranking/windowing functionality - 8.4 documentation link:

WITH summary AS (
   SELECT t.*,
          ROW_NUMBER() OVER(ORDER BY t.published_date) AS position
     FROM TABLE t)
SELECT s.*
  FROM summary s
 WHERE s.id = 65

Alternate without the WITH syntax:

SELECT s.*
  FROM (SELECT t.*,
               ROW_NUMBER() OVER(ORDER BY t.published_date) AS position
          FROM TABLE t) s
 WHERE s.id = 65

The position column will be an integer value representing the location of the record where the id value is 65, based on the published_date column in ascending order. If you want the position value to be duplicated when there are ties, replace ROW_NUMBER() with RANK()

MySQL get row position in ORDER BY

Use this:

SELECT x.id, 
       x.position,
       x.name
  FROM (SELECT t.id,
               t.name,
               @rownum := @rownum + 1 AS position
          FROM TABLE t
          JOIN (SELECT @rownum := 0) r
      ORDER BY t.name) x
 WHERE x.name = 'Beta'

...to get a unique position value. This:

SELECT t.id,
       (SELECT COUNT(*)
          FROM TABLE x
         WHERE x.name <= t.name) AS position,
       t.name    
  FROM TABLE t      
 WHERE t.name = 'Beta'

...will give ties the same value. IE: If there are two values at second place, they'll both have a position of 2 when the first query will give a position of 2 to one of them, and 3 to the other...

How to find row number of a record?

try this

WITH MyTable AS
(
    SELECT ProductDetailNo, ProductDescription,
    ROW_NUMBER() OVER ( ORDER BY ProductDetailNo ) AS 'RowNumber'
    FROM Product
) 
SELECT RowNumber, ProductDetailNo     
FROM MyTable 
WHERE ProductDetailNo = 225

Retrieving the value of a given column index from a SQL Result Set query

Since you are getting the last record as the latest session id, I assume that your order by condition should be ORDER BY SESSION_ID ASC rather than ORDER BY SESSION_ID DESC
Use as below:

       String sql = "SELECT session_id FROM log_table ORDER BY session_id asc"; 

       int position = 0;    // Number of nth Position

        while (resultSet.next()) {

        if (resultSet.last()) 
        {
            position = resultSet.getRow();
            latestSessionID = resultSet.getInt(1);
            //
            latestSessionID = resultSet.getInt("SESSION_ID");
            System.out.println("Number of sessions = " + position + ", Session ID is : " + latestSessionID);
        }

Notes:

• resultSet.getInt(ColumnIndex) takes the columnIndex starting from 1, where the first field of query is 1, 2nd field is 2 and so on.....

• Since you want only 1 record, you can also change your query as below:

  -- For Oracle
  SELECT SESSION_ID FROM LOG_TABLE WHERE ROWNUM=1 ORDER BY SESSION_ID DESC;  
  
  -- For other DBs based on DB,
  SELECT TOP 1 SESSION_ID FROM LOG_TABLE ORDER BY SESSION_ID DESC;
  SELECT SESSION_ID FROM LOG_TABLE ORDER BY SESSION_ID DESC LIMIT 1;
  

---

Related Topics