Jump to content

Archived

This topic is now archived and is closed to further replies.

grandadevans

Retreaving the Recordset Row number

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

Share this post


Link to post
Share on other sites
you gotta read more on 'join' keyword.

Share this post


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

Share this post


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

Share this post


Link to post
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

Share this post


Link to post
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]

Share this post


Link to post
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

Share this post


Link to post
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

Share this post


Link to post
Share on other sites
I'm not such a fan of temporary tables... why not simply make two counters, an increment each one in the its respective subquery?

Share this post


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

Share this post


Link to post
Share on other sites

×

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.