Member Avatar for jameswoodhouse

Hi i'm trying to write a simple SQL command to use within PHP.

I want to select the whole table, sort by one of the columns, then only display the top 100 rows.

Without the WHERE rownum <=10 it works fine. But it displays the whole contents of the table rather than the top 10 from the quantity column.

Thanks

  • 3 Contributors
  • 2 Replies
  • 112 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by saurav.prasad28

Dani AI

Generated

The original issue comes from using Oracle-style filtering (ROWNUM) instead of MySQL syntax and from not tying the LIMIT to a definite sort. As pointed out, MySQL uses LIMIT; gave a similar example. Important: LIMIT only restricts the result set returned by the server — the ORDER BY clause determines which rows are the "top" ones. Without a proper ORDER BY on the correct data type, the rows returned by LIMIT may not be the expected ones.

A common, safe pattern when pulling the top N rows from a table (shown here as a prepared query) binds the row count as an integer so the driver handles quoting correctly:

/* mysqli prepared example: get top 3 by quantity */
$stmt = $mysqli->prepare(
  "SELECT id, name, quantity FROM possessions ORDER BY quantity DESC LIMIT ?"
);
$limit = 3;
$stmt->bind_param('i', $limit);
$stmt->execute();
$res = $stmt->get_result();
while ($row = $res->fetch_assoc()) {
  // output fields from $row
}

If results still look wrong, verify these points: the ORDER BY column is actually numeric (strings sort differently — use CAST if needed), add a secondary column (for example a unique id) to ORDER BY to make ordering deterministic when values tie, run the SQL directly in the MySQL client or use EXPLAIN to see the plan, and confirm PHP is executing the same query being inspected (echo the final SQL or check the prepared statement). Also prefer selecting only required columns instead of SELECT * for performance.

For paging large tables, avoid very large offsets; use keyset pagination (seek method) instead. Example pattern:

SELECT id, name, quantity
FROM possessions
WHERE (quantity < :last_quantity) OR (quantity = :last_quantity AND id > :last_id)
ORDER BY quantity DESC, id ASC
LIMIT 3;

This returns the next 3 rows after the last-seen cursor efficiently and avoids deep OFFSET scans.

Recommended Answers

All 2 Replies

Member Avatar for mwasif

You need to use LIMIT

$sql = 'SELECT * FROM Possesions ORDER BY quantity DESC LIMIT 0,10';
Member Avatar for saurav.prasad28

I think this is helpful for you

select * from employee order by book_id  limit 7;

Thanks

Edited by mike_2000_17 because: Fixed formatting
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.