Solved

how is this query ordered

Posted on 2011-02-24
11
213 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
  • 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:ewangoya
ewangoya 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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:ewangoya
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Error message when scheduling a job using a linked Server 12 61
SSRS report parameters set after publishing to report manager 1 57
Help Required 2 33
Sql Server group by 10 30
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…

831 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