sql server - order by original record order

I'm working with an application that loads a dynamic homepage for each client that uses it.  The homepage is set up from a database table that holds all the controls that are to be loaded into the homepage for each client.  The order of the records in the table is important as they are loaded into the homepage in the order in which they appear in the table.  There is however no column in the table that specifies the order in which the records are to be listed.  It seems to be working off the assumption that the order in which the records are inserted is the correct order and that they will stay in that order.

The problem that I'm running into is that i'm needing to join this table with another table and conserve the loading order.  This is not happening however when I do the join.  I get the correct data back but there doesn't seem to be any logical order conserved from the way the records were listed in the table originally.  And as there is no identity column, there is no way to fix this by doing an order by.

Additionally, I'm not able to add such a column because it would create problems in other places in the applicaiton.

Is there a way to detect the original order of records in a table and order by that?
Who is Participating?
Daniel ReynoldsConnect With a Mentor Software Applications Developer / IntegratorCommented:
Something like the following and then you can join the temp table after you fill it.

CREATE table #HPComp
    PK_ID int IDENTITY (1,1)
    , ProjectID  varchar(50)
    , HomepageGroupID varchar(50)
    , UCName varchar(50)

INSERT INTO #HPComp (ProjectID, HomepageGroupID, UCName)
SELECT ProjectID, HomepageGroupID, UCName
FROM HomePage_Components WITH(NOLOCK)
WHERE ProjectID = 'ALB_CSI' AND HomepageGroupID = 'UnitUser'

Select blah
from #HPComp JOIN blah on blah
Daniel ReynoldsSoftware Applications Developer / IntegratorCommented:
an alternative solution might be to pull your records using your original query (that gives the proper order) into a temp table that has an identity key.

You should then be able to join your temp table to whatever table you want and sort by the identity key on the temp table.
josephdaviskcrmAuthor Commented:
Or can I do something like this?

SELECT ProjectID, HomepageGroupID, UCName, identity(1, 1) AS presedence
FROM HomePage_Components WITH(NOLOCK)
WHERE ProjectID = 'ALB_CSI' AND HomepageGroupID = 'UnitUser'

and join that to my other table?
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

josephdaviskcrmAuthor Commented:
oops, I posted my comment at the same time you posted yours.

Can you show me an example of how one might do what you're suggesting?
In SQL2005 use the rank() command
select rank() OVER (ORDER BY a.au_lname, a.au_fname) as rank, a.au_lname, a.au_fname
   from authors a
   order by rank
REF: http://support.microsoft.com/kb/186133
No such way exists. SQL Server just collects set of records and if you wish some order when working with these records you have to use ORDER BY clause. Processing similar to DBF files from dBase or FoxPro is not applicable.

And, of course, if you require order in which data were entered, you'll need some identity column, timestamp, or some other incremental counter otherwise you are never sure about the right order on output.

If your application is written correctly the new column should not cause any problems to others.
Use SQL similar to what I provided above as one side of your join (without the order by rank) and after the join add the order by rank back in.
Hope this makes sense.
Do you have a clustered index against the original table?

SQL server will normally order records by the clustered index, but there is no guarantee on how the results are ordered unless you use an explicit ORDER BY clause..

You could populate records to a temptable with an identity key (as suggested by xDJR1875), but there is no guarantee that the records will be populated in this temptable in the correct order unless an ORDER BY clause is used..

josephdaviskcrmAuthor Commented:
Jammer, your sql is making me do an order by to establish the rank.  But I don't want to order by any specific column, but just the natural order that the records are showing up out of the table.

SELECT ProjectID, HomepageGroupID, UCName, rank() OVER (ORDER BY ProjectID) as Rank
FROM HomePage_Components WITH(NOLOCK)
WHERE ProjectID = 'ALB_CSI' AND HomepageGroupID = 'UnitUser'

ALB_CSI      UnitUser      banner.ascx                           1
ALB_CSI      UnitUser      BarChartEX.ascx      1
ALB_CSI      UnitUser      BuildReport.ascx      1
ALB_CSI      UnitUser      footer.ascx                           1
ALB_CSI      UnitUser      ImageHolder.ascx      1
ALB_CSI      UnitUser      ImageHolder.ascx      1
ALB_CSI      UnitUser      menu.ascx                           1
ALB_CSI      UnitUser      mostrecentcomment.ascx      1
ALB_CSI      UnitUser      myreports.ascx      1
ALB_CSI      UnitUser      ProgramNews.ascx      1
ALB_CSI      UnitUser      Quickscores.ascx      1
ALB_CSI      UnitUser      ucChart.ascx      1
NOTE:  My suggestion was incorrect.  Please do not award me any points.  You should follow xDJR1875 suggestions.
Sorry for  the confusion.
Gentleman stop blaming the question author.

The physical order in which records are INSERTED does mean NOTHING for subsequent SQL SELECTs from the table. It may work in some situations but no SQL engine can ensure it.

The identity column in the last solution from xDJR1875 will ensure proper order in temporary table #HPComp but not in the INSERT-SELECT command which populates this table.

The only relevant and stable solution is based on new column in table to which records are originally inserted and properly used ORDER BY...

Raja Jegan RSQL Server DBA & ArchitectCommented:
I hope you can achieve it.

Create index on any date column in that table and in ascending order.

Now when you select the records, do an order by on that date column so that you will obtain records sorted based on that inserted date, so in no way you will lose the order.

Even if you want to join that table with some other table, you can use that order by date column which will preserve your order.

Hope this solves your issue and If I am misunderstood kindly correct me.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.