Jump to content


Photo

Retreaving the Recordset Row number


  • Please log in to reply
9 replies to this topic

#1 grandadevans

grandadevans
  • Members
  • PipPipPip
  • Advanced Member
  • 30 posts

Posted 05 June 2006 - 09:30 AM

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

and
SELECT [code required] AS rowNumber, a.otherNumber FROM a ORDER BY a.otherNumber DESC
To come up with
|--------------------------------|
|rowNumber | otherNumber |
|--------------------------------|
|       1         |       3            |
|       2         |       2            |
|       3         |       1            |
|--------------------------------|
I have books on SQL galore but I cannot find it, anybody any ideas?
Thanks
John
Please sign our petition and support our troops<br />
<a href="http://www.postedove...ition.php"><img src="http://www.postedove...o_campaign.jpg" /></a>

#2 hvle

hvle
  • Members
  • PipPipPip
  • Advanced Member
  • 667 posts
  • Locationmelbourne, Australia

Posted 05 June 2006 - 04:03 PM

you gotta read more on 'join' keyword.
Life's too short for arguing.

#3 grandadevans

grandadevans
  • Members
  • PipPipPip
  • Advanced Member
  • 30 posts

Posted 05 June 2006 - 05:01 PM

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
Please sign our petition and support our troops<br />
<a href="http://www.postedove...ition.php"><img src="http://www.postedove...o_campaign.jpg" /></a>

#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 05 June 2006 - 05:06 PM

You'll need to use MySQL variables for this:

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

Hope that helps.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#5 grandadevans

grandadevans
  • Members
  • PipPipPip
  • Advanced Member
  • 30 posts

Posted 05 June 2006 - 08:02 PM

Hi, thanks for your help
When I tried the code
SET @rowNumber := 0;
SELECT @rowNumber=rowNumber+1 AS rowNumber, a.otherNumber FROM a ORDER BY a.otherNumber ASC
It came up with something like 'unknown colum rowNumber'
I tried all sorts of variations including
SELECT @rowNumber=@rowNumber+1;
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
Please sign our petition and support our troops<br />
<a href="http://www.postedove...ition.php"><img src="http://www.postedove...o_campaign.jpg" /></a>

#6 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 05 June 2006 - 08:35 PM

[!--quoteo(post=380351:date=Jun 5 2006, 04:02 PM:name=grandadevans)--][div class=\'quotetop\']QUOTE(grandadevans @ Jun 5 2006, 04:02 PM) View Post[/div][div class=\'quotemain\'][!--quotec--]
Hi, thanks for your help
When I tried the code
SET @rowNumber := 0;
SELECT @rowNumber=rowNumber+1 AS rowNumber, a.otherNumber FROM a ORDER BY a.otherNumber ASC
It came up with something like 'unknown colum rowNumber'
I tried all sorts of variations including
SELECT @rowNumber=@rowNumber+1;
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.
SET @rowNumber := 0;
SELECT @rowNumber:=@rowNumber+1 AS rowNumber, a.otherNumber FROM a ORDER BY a.otherNumber ASC

Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#7 grandadevans

grandadevans
  • Members
  • PipPipPip
  • Advanced Member
  • 30 posts

Posted 06 June 2006 - 07:19 AM

Nice One,
Cheers mate, that works a treat but now I've run into another problem.
The statement I have currently looks like this
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;
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
Please sign our petition and support our troops<br />
<a href="http://www.postedove...ition.php"><img src="http://www.postedove...o_campaign.jpg" /></a>

#8 grandadevans

grandadevans
  • Members
  • PipPipPip
  • Advanced Member
  • 30 posts

Posted 06 June 2006 - 03:00 PM

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
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;
Resulting in
+------+--------------------------------+----------+------+----------+
| 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 |
+------+--------------------------------+----------+------+----------+
Cheers Fanway.
If anybody has any easier ways to do it feel free to let me know.
John
Please sign our petition and support our troops<br />
<a href="http://www.postedove...ition.php"><img src="http://www.postedove...o_campaign.jpg" /></a>

#9 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 06 June 2006 - 05:31 PM

I'm not such a fan of temporary tables... why not simply make two counters, an increment each one in the its respective subquery?
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#10 grandadevans

grandadevans
  • Members
  • PipPipPip
  • Advanced Member
  • 30 posts

Posted 06 June 2006 - 05:34 PM

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
Please sign our petition and support our troops<br />
<a href="http://www.postedove...ition.php"><img src="http://www.postedove...o_campaign.jpg" /></a>




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users