Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


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.
Independent Software Vendors: 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 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article discusses how to create an extensible mechanism for linked drop downs.
This article discusses how to implement server side field validation and display customized error messages to the client.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to dynamically set the form action using jQuery.
Suggested Courses

604 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