An MySQL order problem...

Posted on 2004-10-19
Last Modified: 2008-02-01

I have a table like below:

| shipment-1 |  9 |
| shipment-2 | 12 |
| shipment-3 | 28 |
| shipment-4 | 14 |
| shipment-5 | 10 |
| shipment-6 | 15 |
| shipment-7 | 21 |
| shipment-8 | 17 |

and I want to select 5 row but my real selected row must be in the middle (must be third) and the 2 below and 2 up rows must be numerical close to the middle row... Like in the example below:

| 2 | shipment-7 | 21 |
| 3 | shipment-8 | 17 |
| 4 | shipment-6 | 15 |
| 5 | shipment-4 | 14 |
| 6 | shipment-2 | 12 |

How can I achieve this with a MySQL query? I think first I have to select my real shipment like SELECT * FROM db WHERE shipment='shipment-6'; but then how can I get the up and below 2 rows? And also how can I achieve the order of these 5 rows from the big database? [see second table, leftside of shipments...]

Thank you,
Cem Louis
Question by:cemlouis
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
LVL 13

Expert Comment

ID: 12350108
What version of MySQL are you running?

Author Comment

ID: 12350248
MySQL 4.0.15 (or newer)
LVL 33

Expert Comment

ID: 12350390
Here's a kludgy way using a temporary table, multiple queries, and mysql variables:

SELECT @value:=value FROM tableName WHERE shipment = 6;
CREATE TABLE myTemp SELECT * FROM tableName WHERE shipment = 6;
INSERT INTO myTemp SELECT * FROM tableName WHERE value > @value ORDER BY value LIMIT 2;
INSERT INTO myTemp SELECT * FROM tableName WHERE value < @value ORDER BY value DESC LIMIT 2;
SELECT  * FROM myTemp ORDER BY value;

It might be easier to do it in PHP - you could then skip the temporary table and the mysql variable.

If you upgrade to version 4.1 or later, there may be a better method using subqueries.
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 12350822
Hi snoyes_jw,

ok then when I insert the above rows with these INSERT INTO myTemp SELECT * FROM tableName WHERE value > @value ORDER BY value LIMIT 2; so how can I know my third row's order in the main db?

Author Comment

ID: 12351302

How can I know the row number of my shipment_name if I use a SELECT shipment_name FROM db WHERE shipment_name='shipment-6' ORDER BY value DESC LIMIT 1;

LVL 33

Expert Comment

ID: 12351639
I'm not sure I understand these follow-up questions.

value would be the name of the field holding 9, 12, 28, ... in your example.

The only thing you would need to change for each query (once you get the field names corrected) would be the '6' in the first two.
LVL 33

Accepted Solution

snoyes_jw earned 250 total points
ID: 12358715
If I understand correctly, it would be easiest to do get that number in PHP.

$result = mysql_query($sql) or die(mysql_error()); // $sql is that last query from above
$rowNumber = 0;
while ($row = mysql_fetch_assoc($result)) {
  echo $rowNumber . $row['shipment'] . "<br/>\n";

Author Comment

ID: 12359848
Hi snoyes_jw,

Yes I am doing just like that but I have a half million row on my table. So it takes about five sec to list 11 rows with my selected row in the sixth position...I am wondering if  there is a way to do this with a query. I think MySQL engine is more powerful to do things like that???

Thank you for the efforth indeed...
Cem Louis

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
These days socially coordinated efforts have turned into a critical requirement for enterprises.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question