Mysql -> Assign Id’s to records

Asked

Viewed 52 times

1

So I’m having doubts about something that might not be complicated, I wanted to assign values (id’s) to the records that return from this: 

SELECT * FROM `aldeias` where `userid`='-1'

Doing this will return only the id’s of the unowned villages (-1), but will be their id’s

|ID | ALDEIA | DONO

|1 | Aldeia 1 |-1

|3 | Aldeia 1 |-1

|7 | Aldeia 1 |-1

|8 | Aldeia 1 |-1

I wanted it to stay something like this: 

ID.. |ID | ALDEIA | DONO

1  |1 | Aldeia 1 |-1

2  |3 | Aldeia 1 |-1

3  |7 | Aldeia 1 |-1

4  |8 | Aldeia 1 |-1
php html mysql

  • your ID column that has the numbers out of order is already primary key ?

  • Yeah, that’s right

3 answers

2


Mysql does not have the window functions (window funtions) of SQL Server, so you have to do it manually. I would

SELECT  @rownum := @rownum + 1 AS ID_RN
       ,t.ID 
       ,t.ALDEIA 
       ,t.DONO
  FROM aldeias t, (SELECT @rownum := 0) r
 WHERE userid = -1
  • 1

    Thanks, I got what I wanted :)

  • I will give +1 because I found your answer very good, was at the point that AP needs ;) rsrsrssr

1

The value of the column OWNER to Villages without owner could be 0 and not -1.

Then you would do:

Select * From Aldeias Where Dono = 0

Return all villages that have no owner.

  • I want all records with userid -1 returned with ordered id’s.

0

Actually your problem boils down to the same problem I had here

Mysql - make each row increment 1

I’ll leave an example that solves your problem not yet tested with the names of your table but I checked with my tables here and it worked

select @num := @num + 1 as ID, 'Aldeia 1' as Aldeia,'-1' as Dono from aldeias, (SELECT @num := 0) as t whereuserid='-1';

Browser other questions tagged php html mysql

You are not signed in. Login or sign up in order to post.