mySQL Select statement with generic incrementing value

Question

I have an odd situation I am trying to handle. In a mySQL SELECT query, I need to have a field, not coming from the database, that just counts up from 0 for each record returned.

For background info, I have to query a table of addresses, which currently looks like this:

SELECT 
  addressID, 
  address_1, 
  address_2, 
  city, 
  state, 
  zipcode, 
  country, 
  county, 
  address_type 
FROM employee_address 
WHERE employee_id = 1234

Each record returned opens a new instance of a template of form fields in java, populated with the address info.

But I need a dummy field that just counts up from 0 for each record returned, that can be assigned to each template as an "index", which is used for reference later in my python coding (without this I have no index to go by at a later point in the code). Hopefully something like this

SELECT 
  increment(0) AS templateID, 
  addressID, 
  address_1, 
  address_2, 
  city, 
  state, 
  zipcode, 
  country, 
  county, 
  address_type 
FROM employee_address 
WHERE employee_id = 1234

And unfortunately I don't have access to change the java code to just give the templates an index value. Sad but true.

Giorgos Betsos · Accepted Answer · 2015-06-05 19:48:58Z

1

Use a variable:

SELECT 
  @idx:=@idx+1 AS templateID, 
  addressID, 
  address_1, 
  address_2, 
  city, 
  state, 
  zipcode, 
  country, 
  county, 
  address_type
FROM employee_address, (SELECT @idx:=-1) AS var 
WHERE employee_id = 1234

templateID will start from 0 and then will be incremented by one for each record returned by the query.

answered Jun 5, 2015 at 19:48

Giorgos Betsos

72.3k10 gold badges69 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

MicDunDee Over a year ago

I knew there would be something more advanced, but had no idea what to search for. Thank you Giorgos!

Collectives™ on Stack Overflow

mySQL Select statement with generic incrementing value

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related