• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

SELECT QUERY

 
Sub swamy
Ranch Hand
Posts: 121
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If we issue the statement

select * from <tableName>

in what order will the rows be returned 1) if there is a primary key and 2) if we dont have a primary key/unique key
 
David O'Meara
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
if you don't provide a specific order then it is up to the database. There are no guarantees.
 
Remko Strating
Ranch Hand
Posts: 893
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
From my experience mostly the order is the time that the different records in a table are updated.

You should consult you database information for that.
 
David O'Meara
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
it can come out in this order, but to assume it comes out in this order without providing an explicit ordering would be a mistake.
 
Raghavan Muthu
Ranch Hand
Posts: 3381
Mac MySQL Database Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I think what David said is correct.

By default the database creates an index on a key column (Primary Key). Its up to the programmer to create an index on a non-key column.

The presence of keys in a table does not matter for the ordering of retrieval. Until and unless the ordering is mentioned in the SELECT statement it is NOT considered.

As the database maintains the rows in "heap-ordered" and NOT "index-ordered" and if you dont specify any specific ordering in the SELECT statement, the ordering is purely upto the database. Its not guaranteed!

Hope this helps!
[ August 10, 2007: Message edited by: Raghavan Muthu ]
 
Remko Strating
Ranch Hand
Posts: 893
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You're right always try to have an explicit order, but when I don't provide one my customers always complain that there is no order and I always see that the records are ordered in a way they are updated.

This behavior could arise by the use of temp tables in my stored procedures and where mostly is no index available.

I did a test for a direct select in Sybase, Mysql and Oracle and could see that indeed by defining a primary key the records are ordered that way. I think this is because for the database is more natural to retrieve data ordered by their primary key.
 
Raghavan Muthu
Ranch Hand
Posts: 3381
Mac MySQL Database Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Remko Strating:
I think this is because for the database is more natural to retrieve data ordered by their primary key.


Though it could possibly be tentatively-correct but not exactly. Because the data in the *physical file* is retrieved in the order they reside in the blocks. But we can't take it for granted that it is guaranteed.

In precise, simply having a PK defined for the table really does not matter until its used in the order by column in the SELECT clause to have a real effect.
[ August 14, 2007: Message edited by: Raghavan Muthu ]
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic