SQL Select record order by the date the record was created

Question

I have A Legacy system that runs on a SQL Server 2005.

Current table only have date column YYYYMMDD, it lacks for both a running sequence number and time.

Is it possible to select the a table and order by chronological? or in other words, sort the return result with records are sorted base on the date and time the record was created.

You have to have a script to rearrange the YYYYMMDD first... — henryaaron
– henryaaron, Commented Jan 11, 2012 at 6:58

slayernoah · Accepted Answer · 2016-01-29 20:08:41Z

1

If you do not have any valid time information or other information - like a vector autonumbering (IDENTITY), which was not modified - then the general answer is NO

BUT

The devil is in the details - it depends on database engine you use.

edited Jan 29, 2016 at 20:08

slayernoah

4,51012 gold badges45 silver badges76 bronze badges

answered Jan 11, 2012 at 7:01

Oleg Dok

21.9k4 gold badges48 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Reusable Over a year ago

It is on SQL Server 2005. and another client was using MySQL 4. Does these two database engine classify as part of the devil you mention?

Reusable Over a year ago

i agree with you that general answer is a "NO". however, i would like to count all sort of luck i have in the database before i write a log analyser :( the log files come close to 1TB in total.

Oleg Dok Over a year ago

@Reusable For example there is no decision in MSSQL and there IS a decision in SQLite with its own rowid column IF it was not remapped. You got the point? If engine adds its own info to the records - then you may succeed, else - no

Reusable Over a year ago

we are at the same page. god... i am left with no other choice but the most dirty way on earth --> log

slayernoah · Accepted Answer · 2016-01-29 20:02:11Z

Just so that it might help someone else out. (I realize that that OP mentions MSSQL 2005)

In SQL 2008 and higher, you can use the %%physloc%% virtual column for this. This is undocumented and unsupported. However, it has proven to be helpful in many cases.

The hexadecimal values in %%physloc%% not only indicate the file number and page number; they also specify the slot number within the page. However, the encoding is not really human readable.

The following functions can be used to make sense of the values in this virtual column.

1. The sys.fn_PhysLocFormatter function

This function turns the hex value returned by %%physloc%% into a nice human-readable format. You can use it like this:

SELECT *,sys.fn_PhysLocFormatter(%%physloc%%) AS PLF FROM dbo.tst AS T ORDER BY PLF;

The output of this function has the format (FileNumber:PageNumber:SlotNumer). Below is the output of above query:

2. The sys.fn_PhysLocCracker function

This is a table valued function, you have to use the CROSS APPLY statement to call it within a query:

SELECT * FROM dbo.tst AS T CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%) AS FPLC ORDER BY FPLC.file_id, FPLC.page_id, FPLC.slot_id;

It returns three columns: file_id, page_id and slot_id. Used with the same table you have seen in the previous example, the output looks like this:

References:

Where are my Rows? – Using the %%physloc%% Virtual Column

SQL Server - Find Physical Location of Records

SQL Server 2008: New (undocumented) physical row locator function

Collectives™ on Stack Overflow

SQL Select record order by the date the record was created

2 Answers 2

4 Comments

References:

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

References:

Comments

Related