Solved

how is this query ordered

Posted on 2011-02-24
11
230 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

737 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