Mysql - Assign Id's to records

Question

Navigation

#1 by (2 votes)
#2 by (1 votes)
#3 by (0 votes)

1

So, I'm having doubts about something that might not be complicated, I wanted to assign values (id's) to the records that returned it:

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

Doing this, will return only the id's of the villages without owner (-1), but will be the id's of them

|ID | ALDEIA | DONO

|1 | Aldeia 1 |-1

|3 | Aldeia 1 |-1

|7 | Aldeia 1 |-1

|8 | Aldeia 1 |-1

I wanted it to look 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 mysql html

asked by anonymous 16.06.2015 / 14:01

3 answers

1

The value of the DONO column for Unowned Villages could be 0 and not -1 .

Then you would:

Select * From Aldeias Where Dono = 0

It will return all villages that have no owner.

16.06.2015 / 14:06

0

In fact your problem comes down to the same problem I had here

I will leave an example that solves your problem I have not yet tested with the names of your table but I rode 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 where userid ='-1';

16.06.2015 / 14:11

export json file How to perform a query in HQL in C #?

score 2 · Accepted Answer

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

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