?
Solved

Adding colunm of sequential numbers to results

Posted on 2007-10-02
1
Medium Priority
?
236 Views
Last Modified: 2012-08-14
I am using MySQL 5 stored procedures together with ASP.NET 2.0. In my ASP page I have a sortable GridView. Due to a rather complex ORDER BY in the initial query I would like a add a column to the MySQL results that would just have seqeuntial numbers, which could then be used for sorting by ASP.NET.

Bascally on the original result set I want an extra column "sort" that would just have 1, 2, 3, etc.
0
Comment
Question by:DerekWatling
1 Comment
 
LVL 17

Accepted Solution

by:
Aleksandar Bradarić earned 500 total points
ID: 19997929
You could try using user variable like:
---
mysql> SET @a:=0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT productID, IF(@a>-1, @a:=@a+1, @a:=1) as sort FROM myTarget;
+-----------+------+
| productID | sort |
+-----------+------+
| 1         |    1 |
| 2         |    2 |
| 3333      |    3 |
| 4         |    4 |
| 11        |    5 |
| 22        |    6 |
| 333       |    7 |
|           |    8 |
+-----------+------+
8 rows in set (0.00 sec)

mysql> SET @a:=0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT productID, IF(@a>-1, @a:=@a+1, @a:=1) as sort FROM myTarget ORDER
BY productID;
+-----------+------+
| productID | sort |
+-----------+------+
|           |    1 |
| 1         |    2 |
| 11        |    3 |
| 2         |    4 |
| 22        |    5 |
| 333       |    6 |
| 3333      |    7 |
| 4         |    8 |
+-----------+------+
8 rows in set (0.00 sec)
---
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
The title says it all. Writing any type of PHP Application or API code that provides high throughput, while under a heavy load, seems to be an arcane art form (Black Magic). This article aims to provide some general guidelines for producing this typ…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question