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
Solved

how is this query ordered

Posted on 2011-02-24
11
218 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Trigger selecting another database 4 41
sql query help 2 57
Unable to save view in SSMS 21 73
Why i am getting a star, SSMS does not show me any error. Division Error 5 32
by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

789 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