Jump to content

Retreaving the Recordset Row number


grandadevans
 Share

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.

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.

 Share

×
×
  • 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.