Record Position in the database table

Question

Suppose I have a database table like following

Table: "user"

    u_id  uname
    111  abc
    112  xyz
    113  pqr
    115  mno

I want to ask:

can I know the actual position of the particular record in the table?

For example:

user having uname 'mno' and u_id '115' is having position 4th in the database.

probably you are asking for the serial number of the corresponding row If you order them by u_id. right? — 1000111, Jan 25 '16 at 06:46
depends on your sort order and new entries. It is normally created on the fly as a rank with *variables* as they call them. But at the moment, your question is unclear. — Drew, Jan 25 '16 at 06:53
Luckily, users are in sequential order, so yes. But rows in an RDBMS table have no order so really this question makes no sense — Strawberry, Jan 25 '16 at 08:00

1000111 · Accepted Answer · 2016-01-25T07:11:07.473

3

Probably you are asking for the serial number of the corresponding row If you order them by u_id ascending.

If so here's the query:

SELECT
    u_id,
    uname,
    @a := @a + 1 SL

FROM
    user,   (SELECT @a := 0) serial
ORDER BY u_id ASC

Here's the sample output :

CAUTION: You may get different position number if you sort them based on other field. Since your question gave me a hint that the data are ordered based on u_id ascending so this query suits. Again if you want the position number of your data based on uname field (ascending) the query might look like following:

SELECT
    u_id,
    uname,
    @a := @a + 1 SL

FROM
    user,   (SELECT @a := 0) serial
ORDER BY uname ASC

And sample output would look like:

N:B: Position of the records may also change if new records are inserted.

edited Jan 25 '16 at 07:11

answered Jan 25 '16 at 06:51

1000111

13,169
2
28
37

Right now this is following the route of the primary index one hopes is in place. Best to improve the answer and mention ordering. In other words, your `SL` is dependent on the ordering, and never set in stone. So I would back away from that concept (or else I get your serial number next time thru). – Drew Jan 25 '16 at 06:55
would you please clarify? @Drew – 1000111 Jan 25 '16 at 06:56
There is no mention of cautionary words of how the `SL` changes as the data changes. And if other decisions (read: inserts) are performed using the above data, links and integrity can get messed up fast. – Drew Jan 25 '16 at 06:59
@Drew, I've added some cautionary words. Please let me know if it's okay. – 1000111 Jan 25 '16 at 07:07
Also note the use of the word `serial` to most people would designate a value that does not change. Unlike terminology like rank or position used by bekt that denotes change (like a report, or a game server ranking). – Drew Jan 25 '16 at 07:11

score 0 · Answer 2 · edited May 23 '17 at 11:45

0

Try this on

SELECT  @s:=@s+1 position,user.*
FROM user,
    (SELECT @s:= 0) AS s
ORDER BY u_id ASC;

MySQL query to select results with auto increment as a new column added in the result

edited May 23 '17 at 11:45

Community

1
1

answered Jan 25 '16 at 06:52

bekt

597
4
16

score 0 · Answer 3 · answered Jan 25 '16 at 07:13

Database : postgresql

select u_id, uname, row_number() OVER () as rnum from table1

Note : If you want row number then use row_number function of postgresql, if you want exact row number then you have to add new field in your table like id (primary key, autoincrement)

Record Position in the database table

3 Answers3