Retreaving the Recordset Row number

[grandadevans]

Hi all,
I have a question, is there a way of retreaving the row number of a Recordset Row number eg
[code]SELECT [code required] AS rowNumber, a.otherNumber FROM a ORDER BY a.otherNumber ASC[/code]
To come up with
[code]
|--------------------------------|
|rowNumber | otherNumber |
|--------------------------------|
|       1         |       1            |
|       2         |       2            |
|       3         |       3            |
|--------------------------------|[/code]

and
[code]SELECT [code required] AS rowNumber, a.otherNumber FROM a ORDER BY a.otherNumber DESC[/code]
To come up with
[code]
|--------------------------------|
|rowNumber | otherNumber |
|--------------------------------|
|       1         |       3            |
|       2         |       2            |
|       3         |       1            |
|--------------------------------|[/code]
I have books on SQL galore but I cannot find it, anybody any ideas?
Thanks
John

[hvle]

you gotta read more on 'join' keyword.

[grandadevans]

Sorry mate,
Ive read looked all through all the documentaion I have on SQL and I can't find an answer anywhere or even figure out how JOINs can help.
Could you please explain more or point me more in the right direction
Thanks again,
John

[fenway]

You'll need to use MySQL variables for this:

[code]SET @rowNumber := 0;
SELECT @rowNumber=rowNumber+1 AS rowNumber, a.otherNumber FROM a ORDER BY a.otherNumber ASC[/code]

Hope that helps.

[grandadevans]

Hi, thanks for your help
When I tried the code [code]SET @rowNumber := 0;
SELECT @rowNumber=rowNumber+1 AS rowNumber, a.otherNumber FROM a ORDER BY a.otherNumber ASC[/code]
It came up with something like 'unknown colum rowNumber'
I tried all sorts of variations including
[code]SELECT @rowNumber=@rowNumber+1;[/code]
But that just keeps coming up with '0' as the answer.
Do you have any idea why or what I can do
I have tried setting the @rowNumber in a subquery but it won't let me.

John

[fenway]

[!--quoteo(post=380351:date=Jun 5 2006, 04:02 PM:name=grandadevans)--][div class=\'quotetop\']QUOTE(grandadevans @ Jun 5 2006, 04:02 PM) [snapback]380351[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Hi, thanks for your help
When I tried the code [code]SET @rowNumber := 0;
SELECT @rowNumber=rowNumber+1 AS rowNumber, a.otherNumber FROM a ORDER BY a.otherNumber ASC[/code]
It came up with something like 'unknown colum rowNumber'
I tried all sorts of variations including
[code]SELECT @rowNumber=@rowNumber+1;[/code]
But that just keeps coming up with '0' as the answer.
Do you have any idea why or what I can do
I have tried setting the @rowNumber in a subquery but it won't let me.

John
[/quote]
Sorry, my bad... it's been a long day.
[code]SET @rowNumber := 0;
SELECT @rowNumber:=@rowNumber+1 AS rowNumber, a.otherNumber FROM a ORDER BY a.otherNumber ASC[/code]

[grandadevans]

Nice One,
Cheers mate, that works a treat but now I've run into another problem.
The statement I have currently looks like this
[code]
SELECT b.company, b.companyID, @posThisWeek :=@posThisWeek+1 AS posThisWeek,
  (SELECT COUNT(c.clickID) FROM click_log c
  WHERE c.companyID=b.companyID AND WEEK(c.`date`)=WEEK(CURDATE())-1
  GROUP BY c.companyID LIMIT 1) AS clicksLastWeek,
  (SELECT COUNT(c.clickID) FROM click_log c
  WHERE c.companyID=b.companyID AND WEEK(c.`date`)=WEEK(CURDATE())
  GROUP BY c.companyID LIMIT 1) AS clicksThisWeek
FROM bfpomerchants b ORDER BY clicksThisWeek DESC LIMIT 10;[/code]
This gives me the top 10 with number of clicks per company for the last 2 full weeks.
My problem is that I can't figure out how to get the position of
'clicksLastWeek'
as every time I try the @ statement it show the record number (obviously, and that is what I asked for!)
It works fine for this weeks position but do you know how I could get the position of the week before, Ive tried it in subqueries but can't do it with only 1 column.

Any ideas.
Thanks again
John

[grandadevans]

GOT IT!

The books have come in handy for once.
The solution I have is to create a couple of temporary tables then use them like so
[code]
SET @posLastWeek :=0;

SET @posThisWeek :=0;

DROP TABLE IF EXISTS lastWeek;

DROP TABLE IF EXISTS thisWeek;

CREATE TEMPORARY TABLE lastWeek

SELECT @posLastWeek :=@posLastWeek+1 AS pos, c.companyID, COUNT(c.clickID) AS clicksLW
    FROM click_log c WHERE WEEK(c.`date`)=WEEK(CURDATE())-1 AND companyID !=0
    GROUP BY c.companyID ORDER BY clicksLW DESC LIMIT 10;

CREATE TEMPORARY TABLE thisWeek

SELECT @posThisWeek :=@posThisWeek+1 AS pos, c.companyID, COUNT(c.clickID) AS clicksTW
    FROM click_log c WHERE WEEK(c.`date`)=WEEK(CURDATE()) AND companyID !=0
    GROUP BY c.companyID ORDER BY clicksTW DESC LIMIT 20;

SELECT * FROM lastWeek;

SELECT * FROM thisWeek;

SELECT t.pos, b.company, t.clicksTW, l.pos, l.clicksLW
    FROM thisWeek t
        LEFT JOIN lastWeek l USING (companyID)
        LEFT JOIN bfpomerchants b USING (companyID)
    ORDER BY t.pos ASC;

DROP TABLE lastWeek;

DROP TABLE thisWeek;[/code]
Resulting in
[code]
+------+--------------------------------+----------+------+----------+
| pos  | company                        | clicksTW | pos  | clicksLW |
+------+--------------------------------+----------+------+----------+
|    1 | Let's Go Retro                 |       12 | NULL |     NULL |
|    2 | Surplus and Adventure Ltd.     |       11 |    1 |       64 |
|    3 | Amazon                         |       11 |   10 |        9 |
|    4 | Woolworths                     |        9 | NULL |     NULL |
|    5 | Sports Warehouse               |        6 | NULL |     NULL |
|    6 | Primrose Aromatherapy          |        6 | NULL |     NULL |
|    7 | Sportsshoes Unlimited          |        5 | NULL |     NULL |
|    8 | Mean and Green                 |        5 | NULL |     NULL |
|    9 | Marks and Spencers             |        4 | NULL |     NULL |
|   10 | Island-Leisure                 |        4 | NULL |     NULL |
|   11 | Teddy Love                     |        4 | NULL |     NULL |
|   12 | Spritz                         |        4 | NULL |     NULL |
|   13 | Gadget Pages Ltd               |        3 | NULL |     NULL |
|   14 | Bookplace Ltd                  |        3 | NULL |     NULL |
|   15 | Penrith Survival Equipment Ltd |        3 | NULL |     NULL |
|   16 | BrightMinds                    |        3 | NULL |     NULL |
|   17 | Moonpig                        |        3 |    5 |       17 |
|   18 | Physio Supplies                |        3 | NULL |     NULL |
|   19 | Clean Store                    |        3 | NULL |     NULL |
|   20 | Cadbury Gifts Direct           |        2 | NULL |     NULL |
+------+--------------------------------+----------+------+----------+[/code]
Cheers Fanway.
If anybody has any easier ways to do it feel free to let me know.
John

[fenway]

I'm not such a fan of temporary tables... why not simply make two counters, an increment each one in the its respective subquery?

[grandadevans]

I tried several combinations of that but couln't do it with getting a 1,2,3,4,5 etc no matter how I ordered the subquery
John