Why do results from a SQL query not come back in the order I expect?

by

The order of a query can be forced by using an ‘Order by’ Clause in the statement. A SQL Database does not actually understand what order you put things in, or store the data in a given order. This means that you need to tell SQL what order you want the items in. For instance:

Select * from Table
  order by column1 desc

Think about it like handing some stuff to your friend to hold – she will have all of it for you later, but she stores it someplace in the mean time. She may move it around while you are not looking to make room for something else, or may hand it back in the same order you gave it to her, but you didn’t tell her to keep it in order, so she doesn’t.

Databases need to be able to move things around in the background, so the way they are built does not intrinsically know about any order – you need to know the order when you give it to the database, so that you can put it back in the order you want later. The order clause allows SQL to impose an order on the data, but it doesn’t remember or have one on its own.

Important point: Even when SQL returned the items in the correct order without an order by statement the last 1 million times, it does not guarantee that it will do so. Even if a clustered index exists on the table, the results are not guaranteed to be returned in the order you expect. Especially when SQL versions change, not explicitly using an order by clause can break programs that assume the query will be in the order they want!

More Related Contents:

  1. SQL: Repeat a result row multiple times, and number the rows
  2. Best practices for SQL varchar column length [closed]
  3. How do I UPDATE a row in a table or INSERT it if it doesn’t exist?
  4. how to cast the hexadecimal to varchar(datetime)?
  5. What are the max number of allowable parameters per database provider type?
  6. SQL Query needed for fetch value from Two identical table but not fully identical table
  7. sql query to modify the selected value from database [closed]
  8. Calculate Average of the largest values in multiple columns
  9. SQL Print the name of all Employees together with the name of their supervisor
  10. How to filter SQL results in a has-many-through relation
  11. How to select the nth row in a SQL database table?
  12. Group by clause in mySQL and postgreSQL, why the error in postgreSQL?
  13. Storing JSON in database vs. having a new column for each key
  14. Simple Random Samples from a Sql database
  15. How do I decide when to use right joins/left joins or inner joins Or how to determine which table is on which side?
  16. When to use SELECT … FOR UPDATE?
  17. Relationship between catalog, schema, user, and database instance
  18. What resources exist for Database performance-tuning? [closed]
  19. How to transpose rows to columns with large amount of the data in BigQuery/SQL?
  20. Are SELECT type queries the only type that can be nested?
  21. Postgres LEFT JOIN with WHERE condition
  22. Equivalent of MySQL ON DUPLICATE KEY UPDATE in Sql Server
  23. What happens when I exhaust a bigint generated key? How to handle it?
  24. Why in SQL NULL can’t match with NULL?
  25. Convert SQL Server query to MySQL [duplicate]
  26. comparison of truncate vs delete in mysql/sqlserver [duplicate]
  27. SELECTING with multiple WHERE conditions on same column
  28. How to find gaps in sequential numbering in mysql?
  29. MySQL: Invalid use of group function
  30. Import MySQL dump to PostgreSQL database

Leave a Comment