Solved

how is this query ordered

Posted on 2011-02-24
11
226 Views
Last Modified: 2012-05-11

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[productimages](
      [productimagesid] [int] IDENTITY(1,1) NOT NULL,
      [productid] [int] NOT NULL,
      [name] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF





select * from productimages where productid=15
select * from productimages where productid=14


I dont know how this is ordered
it is not ordered by 'name' or 'productimageid'


do not tell me how to order.  I am using a content management system that is using this one query

0
Comment
Question by:rgb192
[X]
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
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 10

Accepted Solution

by:
dwe761 earned 167 total points
ID: 34971182
Without an OrderBy clause in the query,  the ordering cannot be guaranteed.  You may get different ordering if you run the same query multiple times.
0
 
LVL 26

Assisted Solution

by:tigin44
tigin44 earned 167 total points
ID: 34971298
if any order by listing not defined then usually data retrived in the order they are recorded in the pages...
If you have a primary key with clustered index then the data probably will be listed in that order.
But these are not guaranteed.. You should specify the order info to get the data in the order you wanted...
0
 
LVL 32

Assisted Solution

by:Ephraim Wangoya
Ephraim Wangoya earned 166 total points
ID: 34971415
If you don't specify the order by clause
SQL server tends to get the records in the order they were entered into the table
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 10

Expert Comment

by:dwe761
ID: 34971554
Some good, specific examples: (all examples are MS SQL server)

    *   Dave Pinal's blog describes how two very similar queries can show a different apparent order, because different indexes are used:

      SELECT ContactID FROM Person.Contact
      SELECT *         FROM Person.Contact

    *  Conor Cunningham shows how the apparent order can change when the table gets larger (if the query optimizer decides to use a parallel execution plan).

    *  Hugo Kornelis proves that the apparent order is not always based on primary key. Here is his follow-up post with explanation.

Source: here
0
 

Author Comment

by:rgb192
ID: 34972009
what I meant to ask

is there anything in the productimages table that determines the order  
productimages looks like a regular table to me
0
 
LVL 10

Expert Comment

by:dwe761
ID: 34972027
No
0
 
LVL 10

Expert Comment

by:dwe761
ID: 34972087
I know you didn't ask for alternate solutions, (and I don't know much about CMS) but would it be possible for your content management system to access the data by calling a stored procedure rather than querying the table directly?  That way you could have the sort order specified in the stored procedure.
0
 

Author Comment

by:rgb192
ID: 34974328
how could I check to see if this is a stored procedure
using sql server 2005 enterprise
0
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 34974378

This is not a stored procedure, its table you created
0
 
LVL 10

Expert Comment

by:dwe761
ID: 34974434
I'm not sure I understand your question.  I thought you had a table named productimages and from your CMS, you do queries against that table and you want to ensure the results come back in a particular order?

I was suggesting you could create a stored procedure that does the query for you and you'd just pass the parameter(s).  For example:


CREATE PROC usp_ProductImages(
	@ProdID int
) AS

select * from productimages where productid=@ProdID
ORDER BY productid, name     -- or change this line to whatever you want


-- So instead of doing:
	select * from productimages where productid=15
-- Call the stored proc
	EXEC usp_ProductImages 15

Open in new window

0
 

Author Closing Comment

by:rgb192
ID: 35031841
all led to my understanding that this query is not ordered
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

740 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