Jump to content

Retreaving the Recordset Row number


grandadevans

Recommended Posts

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
Link to comment
Share on other sites

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
Link to comment
Share on other sites

[!--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]
Link to comment
Share on other sites

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
Link to comment
Share on other sites

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
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.