Dream.In.Code > Programming Help > Databases

Welcome to Dream.In.Code
	Become an Expert! Join 149,501 Programmers for FREE! Get instant access to thousands of experts, tutorials, code snippets, and more! There are 1,382 people online right now. Registration is fast and FREE... Join Now! Chat LIVE With a Expert

MySQL: Retrieve row position in returned values

Reply to this topic

Start new topic

MySQL: Retrieve row position in returned values

girasquid	4 Apr, 2007 - 11:03 AM Post #1
Barbarbar Joined: 3 Oct, 2006 Posts: 1,294 Thanked: 18 times Dream Kudos: 725 My Contributions	Hello, all. I am currently working on a browsergame where users are ranked against each other based on the size of their armies. The query below returns all user ID's and user names, in the proper order. CODE SELECT id, username FROM user_stats ORDER BY (SELECT SUM(unit_amount) FROM user_armies WHERE commander = user_stats.id) DESC LIMIT ?,? This is all well and good, but I am looking for a way to retrieve exactly what position in the retrieved results a user might have, in order to get their user-specific rank. Does anyone know of a way that I could do this? Thanks, Girasquid
	This Post Was Helpful!


Register for Free to Make These Ads Go Away!

snoj	RE: MySQL: Retrieve Row Position In Returned Values 4 Apr, 2007 - 11:13 AM Post #2
Fell off the face of the earth Joined: 31 Mar, 2003 Posts: 3,325 Thanked: 9 times Dream Kudos: 750 My Contributions	You can do the same thing you're doing for the ORDER BY in the SELECT part of the "root" query. SQL SELECT something, (SELECT aField FROM someTable2 WHERE whatever) AS aName FROM someTable1
	This Post Was Helpful!

girasquid	RE: MySQL: Retrieve Row Position In Returned Values 4 Apr, 2007 - 02:23 PM Post #3
Barbarbar Joined: 3 Oct, 2006 Posts: 1,294 Thanked: 18 times Dream Kudos: 725 My Contributions	While that query does retrieve the user's army size for me, it's not quite what I'm looking for. I need to find out how many users have a larger army than a specific user.
	This Post Was Helpful!

snoj	RE: MySQL: Retrieve Row Position In Returned Values 4 Apr, 2007 - 02:38 PM Post #4
Fell off the face of the earth Joined: 31 Mar, 2003 Posts: 3,325 Thanked: 9 times Dream Kudos: 750 My Contributions	Well the awesome thing about nested queries is that you can use variables outside of the query you are in. SQL SELECT something1, (SELECT COUNT(t2.userid) FROM aTable1 AS t2 WHERE 1 HAVING t2.armysize > t1.armysize) AS rank FROM aTable1 AS t1 Also, read up on SQL Subqueries http://dev.mysql.com/doc/refman/5.0/en/subqueries.html. It has all the info you'll need to accomplish your task.
	This Post Was Helpful!

Fast Reply

Reply to this topic

Start new topic

Display Mode: Standard · Switch to: Linear+ · Switch to: Outline

Track this topic · Email this topic · Print this topic · Subscribe to this forum

Time is now: 1/7/09 06:35PM

Invision Power Board v2.1.7 © 2009 IPS, Inc.

Licensed to: MediaGroup1

Advertising | Terms of Use | Privacy Policy | About Us | Site Map

Forum Index: Programming Help | C and C++ | Visual Basic | Java | VB.NET | C# | ASP.NET | PHP | ColdFusion | Perl and Python | Ruby | Databases | Other Languages | Game Programming | Software Development | Computer Science | Industry News | Programming Tutorials | C++ Tutorials | Visual Basic Tutorials | Java Tutorials | VB.NET Tutorials | C# Tutorials | Linux Tutorials | PHP Tutorials | ColdFusion Tutorials | Windows Tutorials | HTML/JavaScript Tutorials | CSS Tutorials | Flash Tutorials | Web Promotion Tutorials | Photoshop Tutorials | Software Development Tutorials | Database Tutorials | Other Language Tutorials | ASP.NET Tutorials |

Copyright 2001-2008 MediaGroup1 LLC, All Rights Reserved
A MediaGroup1 LLC Production - Version 6.0.2.1.36
Server: secure2

Search^Updated!w00t

Over 509,000 Pages! Advanced Forum Search

Be Social

Live Help!

Tutorials

Programming

Web Development

Reference Sheets

Code Snippets

DIC Chatroom

Join our IRC Chat

Bye Bye Ads

Monthly Drawing

Top Contributors

Top 10 Kudos This Month