Welcome!

Join our community of MMO enthusiasts and game developers! By registering, you'll gain access to discussions on the latest developments in MMO server files and collaborate with like-minded individuals. Join us today and unlock the potential of MMO server development!

Join Today!

[help]select second place

Joined
Mar 17, 2007
Messages
736
Reaction score
266
PHP:
<?$query=mssql_query("select TOP 1 * from MuOnline.dbo.Character Where ctlcode !='32' and ctlcode !='8' order by PkCount desc");while($row=mssql_fetch_assoc($query)){$namez2=$row['Name'];$pk2=$row['PkCount'];$resetsz2=$row['Resets'];$clevel2=$row['cLevel'];?>                            			                          				<b ><?=$pk2;?></b><br/>				<b><?=$namez2;?></b><br/>				<b>						<?=$clevel2;?> kills<br/>						<?=$resetsz2;?> death</b>				</span><?
}?>


now it select first character , i want to select second one ...
 
Initiate Mage
Joined
Oct 10, 2011
Messages
27
Reaction score
14
PHP:
$query=mysql_query("SELECT * FROM table LIMIT 1, 1");

Limit 1,1 will select the second record.
 
Praise the Sun!
Member
Joined
Dec 4, 2007
Messages
2,502
Reaction score
986
PHP:
$query=mysql_query("SELECT * FROM table LIMIT 1, 1");

Limit 1,1 will select the second record.

MSSQL, not MySQL.

@OP, select top 2, then skip the first row by fetching it or use mssql_result at the second row.
 
• ♠️​ ♦️ ♣️ ​♥️ •
Joined
Mar 25, 2012
Messages
909
Reaction score
464
MSSQL, not MySQL.

@OP, select top 2, then skip the first row by fetching it or use mssql_result at the second row.

bad practice! imagine u want user no. 1000 in mssql u have to get all 1k entries just to get one. the right solution is to filter it by using SQL.
unfortunately mssql does not have any LIMIT command, but there is a work around:

_CodeJack_@planet-source-code.com said:
Code:
				The following query will emulate a 


SELECT emp_id,lname,fname FROM employee LIMIT 20,10


That says, give me 10 records starting at record 21. So what will be returned are rows 21-30. This is used heavily in web-based apps so you can do recordset paging.



Here is what you can do in MS SQL to emulate it (this runs on the PUBS db):



select * from (

  select top 10 emp_id,lname,fname from (

     select top 30 emp_id,lname,fname

     from employee

    order by lname asc

  ) as newtbl order by lname desc

) as newtbl2 order by lname asc




The main thing to remember is to deal with all your ORDER BY clauses and also to use the inner TOP as the sum of what you are looking for. In this example - 30 since you start at 20 and want 10 rows (20+10=30).


Hope this helps you as it has helped me improve performance greatly on my web-based apps since I no longer have to transfer loads of data across the wire to get just the 10 results I display on a page.


Don't even think about using the internal ADO paging stuff if you have a lot of records to deal with, it will kill your performance as it must pull the entire recordset down to the local machine before it can do its work. Why bring down 20,000 records if you only want to display 10?


Enjoy!

Source:


however, ive never tested it (i use just the entity framework for such things). so you should read the comments on the source page, there are even some hints to improve this solution.


have fun.
 
Pee Aitch Pee
Joined
Mar 30, 2011
Messages
630
Reaction score
422
What about:
PHP:
SELECT TOP (1) *
FROM table
WHERE (id NOT IN (SELECT TOP (1) id FROM table ORDER BY id))
ORDER BY id

You'll have to change the query to fit your needs. The NOT IN clause is important in this case.
 
Praise the Sun!
Member
Joined
Dec 4, 2007
Messages
2,502
Reaction score
986
bad practice! imagine u want user no. 1000 in mssql u have to get all 1k entries just to get one.

Yeah, if only OP wanted #1000 and not #2...

What about:
PHP:
SELECT TOP (1) *
FROM table
WHERE (id NOT IN (SELECT TOP (1) id FROM table ORDER BY id))
ORDER BY id

You'll have to change the query to fit your needs. The NOT IN clause is important in this case.

Selecting TOP 2 would be better performance than using nested TOP 1 query's.
 
Back
Top