Auto Increment on the fly...

[John Thorne]

Have a query:
SELECT sum(Sales)AS TotSales, SalesManager
FROM Data
WHERE Region = 'East'
GROUP BY SalesManager
ORDER BY TotSales DESC

Is there a way of creating an autoincrement field

SELECT sum(Sales)AS TotSales, SalesManager, something like
...autoincrement( xxx) AS Rank
FROM Data
WHERE Region = 'East'
GROUP BY SalesManager
ORDER BY TotSales DESC

100 John 1
75 Mary 2
50 Ted 3

etc....

thanks

jrt

[Bruce Wolk]

John Thorne wrote:

Have a query:
SELECT sum(Sales)AS TotSales, SalesManager
FROM Data
WHERE Region = 'East'
GROUP BY SalesManager
ORDER BY TotSales DESC

Is there a way of creating an autoincrement field

SELECT sum(Sales)AS TotSales, SalesManager, something like
..autoincrement( xxx) AS Rank
FROM Data
WHERE Region = 'East'
GROUP BY SalesManager
ORDER BY TotSales DESC

100 John 1
75 Mary 2
50 Ted 3

etc....

thanks

jrt

Use a user variable. Something like

SELECT @m:=0;
SELECT sum(Sales) AS TotSales, SalesManager, @m:=@m+1 AS Rank
etc.

http://www.mysql.com/doc/en/Variables.html

[Bruce Wolk]

John Thorne wrote:

Have a query:
SELECT sum(Sales)AS TotSales, SalesManager
FROM Data
WHERE Region = 'East'
GROUP BY SalesManager
ORDER BY TotSales DESC

Is there a way of creating an autoincrement field

SELECT sum(Sales)AS TotSales, SalesManager, something like
..autoincrement( xxx) AS Rank
FROM Data
WHERE Region = 'East'
GROUP BY SalesManager
ORDER BY TotSales DESC

100 John 1
75 Mary 2
50 Ted 3

etc....

thanks

jrt

Use a user variable. Something like

SELECT @m:=0;
SELECT sum(Sales) AS TotSales, SalesManager, @m:=@m+1 AS Rank
etc.

http://www.mysql.com/doc/en/Variables.html