A mysql row problem???

[cemlouis]

Hi,

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

Thank you,
Cem Louis

 

[r937]

there is no such concept as row number

can you perhaps restate your question? what does "queue" mean?

rudy

http://r937.com/

SQL Consulting

 

[cemlouis]

OK let me explain again sorry for my english... When I reorder the table with the above query the desired row changes in the listing... So first, it was lining in row 3 after the query it appears in row 121 or something else because of the descending order... My question is how can I get the new row number (121) with a single query? I can do this with php but takes long time to calculate because I have a 423577 lined table...

Thank you indeed,
Cem Louis

 

[r937]

the rows in the table do not have a row number, nor are they always in the same position!! there is no concept of order in a table

a relational database table is not the same as a flat file where one row comes after another and you can always tell which row is the first one

the rows in a relational database table have no fixed order

the only time "order" has a concept is when you use ORDER BY in a query

sorry, but that's just the way it is in a relational database



rudy

http://r937.com/

SQL Consulting

 

[cemlouis]

Ok Rudy, First thank you for the quick response you are great(because you answered my question that I posted before)

Anyway forget my last question... I have a table like below:

name value
-------------
| row1 | 9 |
| row2 | 12 |
| row3 | 28 |
| row4 | 14 |
| row5 | 10 |
| row6 | 15 |
| row7 | 21 |
| row8 | 17 |
-------------

I want to select 2 row which has a value bigger than 10 so I wrote the below query

SELECT value, value > 10 FROM shipment_db ORDER BY value DESC LIMIT 2;

but this query returns below:

name value
-------------
| row3 | 28 |
| row7 | 21 |
-------------

The biggest valued rows which has a value bigger than 10... But I want the nearest bigger values like below:

name value
-------------
| row4 | 14 |
| row2 | 12 |
-------------

So how can I achieve that???

Thank you much,
Cem Louis

 

[r937]

select name, value
  from shipment_db 
 where value > 10
order 
    by value asc 
limit 2

rudy

http://r937.com/

SQL Consulting

 

[cemlouis]

Thank you it is working