An MySQL order problem...

Posted on 2004-10-19
Medium Priority
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.

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.


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 1000 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

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!

Question has a verified solution.

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

Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this. Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it i…
Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses

770 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