Solved

how is this query ordered

Posted on 2011-02-24
11
190 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 10

Expert Comment

by:dwe761
Comment Utility
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
Comment Utility
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 10

Expert Comment

by:dwe761
Comment Utility
No
0
 
LVL 10

Expert Comment

by:dwe761
Comment Utility
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
Comment Utility
how could I check to see if this is a stored procedure
using sql server 2005 enterprise
0
 
LVL 32

Expert Comment

by:ewangoya
Comment Utility

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

Expert Comment

by:dwe761
Comment Utility
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
Comment Utility
all led to my understanding that this query is not ordered
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

728 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now