CODEWITHSUNDEEP
mysqldatabase
Vish
Vish

Reputation: 280

Record Position in the database table

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.

Upvotes: 2

Views: 562

Answers (3)

Monty Khanna
Monty Khanna

Reputation: 1120

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)

Upvotes: 0

1000111
1000111

Reputation: 13519

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 :

enter image description here

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:

enter image description here

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

Upvotes: 3

bekt
bekt

Reputation: 597

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

Upvotes: 0

Related Questions