Query sort order changes... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Query sort order changes...

Author

Topic

mrpush
Starting Member

14 Posts

Posted - 2013-03-08 : 14:33:56

Hi,

I am running a query on a table to create 3 other tables.

I have an "insert into" line in the final queries to put a single line of "differently formatted information" into the tables and I change the formats with alter commands so SQL does not err.

Here is the insert:


insert into mytable
values ('E', YEAR(GETDATE()), DATENAME(quarter, GETDATE()), '123456789', '0', 'name', 'address', 'city', 'state', '15597', '720301')

PROBLEM: For one of my 3 identical code queries(different values), sometimes my INSERT record is the first record in the table, sometimes it is dead last. I need it to always be entered as the first record. (so I can CSV it out to file)

This behavior changes when I change date ranges that create the starting table, sometimes its record 1, sometimes is the last record.

This is the full query:

select emp_number, emp_name, emp_addr, emp_city, emp_state, Emp_zip, year, qtr, SumEFed, SumLW, PSD_Code
into mytable1
from mydatatable
where emp_branch = 5
ORDER BY 1


ALTER TABLE mytable1 ALTER COLUMN sumefed varchar (8) NOT NULL
go
ALTER TABLE mytable1 ALTER COLUMN sumlw varchar (8) NOT NULL
go
ALTER TABLE mytable1 ALTER COLUMN year varchar (50) NOT NULL
go
ALTER TABLE mytable1 ALTER COLUMN qtr varchar (50) NOT NULL
go

insert into mytable1
values ('E', YEAR(GETDATE()), DATENAME(quarter, GETDATE()), '123456789', '0', 'name', 'address', 'city', 'PA', '15597', '720301')

How can changing the date range change the sort position of the newly inserted row?

Thanks much,

Mark

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-03-08 : 14:39:24

The position of the row in the table is irrelevant. What matters is adding an ORDER BY to get the ordering you want to be displayed. So if your CSV file is required to be in a certain order, then use an ORDER BY when getting the data out.

You do have an ORDER BY with your initial query, but what I'm saying is you need an ORDER BY for the query where you are selecting from your new table.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

mrpush
Starting Member

14 Posts

Posted - 2013-03-08 : 16:11:34

Tara,

I do have an "order by" in my queries, both the original and the one that creates these last tables.

So then from what you say, if I "order by 1" which I have done in all 3, then all three should have this INSERT record at the end of the table as the original data types are numeric "023456" and the INSERT record is and 'E' in my insert line of varchar(9) allow nulls.

So then, 2 of my tables are doing it wrong and one correctly(at times) then?

How does SQL default to sorting? And it still does not explain why changing dates changes the INSERT record position?

I'm going to try to tinker with the ORDER BY and see what happens.

Thanks,

Mark

mrpush
Starting Member

14 Posts

Posted - 2013-03-08 : 16:22:50

Ok, so all 3 of my queries use "ORDER BY 1" when they are created but only one still messes up depending on date range data in the originating table. (depending on date range, sometimes the INSET record is at top where I want it)

the 2 look like this with ORDER BY 1:

col1
1 E
2 023456
3 034567

the 3rd like this:

col1
285 640123
286 644123
287 E

If I do a ORDER BY 1 DESC I get this:

col1
1 E
2 644123
3 640123

It is like for the 2 it sees the "E" as one format type and for the 3rd it sees it as something else?

I DO NOT have any KEYS set, could that be the problem?

(um, how to set key in query?)

You said the INSERT position does not matter but SQL has to have some default, either 1st record or last, no?

Thanks,

Mark

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-09 : 01:16:03

Nope..unless you specify an order by what effectively happens is you returning records randomly ie order cant be guaranteed

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mrpush
Starting Member

14 Posts

Posted - 2013-03-11 : 09:52:18

Hi,

Again, I can't agree with this. I have ORDER BY clauses in all my queries and One query result STILL messes up the order.

These are fired from a stored procedure, could this be messing it up?

Thanks much,

Mark

mrpush
Starting Member

14 Posts

Posted - 2013-03-11 : 10:22:52

Hi,

My issue here has something to do with the data. Again, I have ORDER BY specified when all 3 tables are created. The 3 tables have a "where =" line in them that refers to a different branch, that is their only difference in query.

On the table that does not sort correctly, if I change my where = X to Y or Z, it sorts correctly!!!! Same works in reverse, I change the where = y or z to x, THEY NO LONGER SORT CORRECTLY and put my insert into line at the bottom and not the top.

This happens EVEN IF I order by on a different column!

Why would this happen? I can only figure there is something in the source data table but how could that be?

It should not care what the source is, right? Again, if I change date ranges for the source table, certain date ranges used and they all sort fine!! I'm Pulling my hair out!

Thanks much,

Mark

mrpush
Starting Member

14 Posts

Posted - 2013-03-11 : 12:22:01

Ok,

So I figure if I take the data in this odd table that will not sort correctly, and I insert it into another table USING an ORDER BY, it should create the new table in the correct sort order, right?

No, it does not!

I understand this whole "relational DB's don't physically sort tables" but they actually DO.

Is there a way for me to get this one row physically in a table at the position I want (the top spot)?

Thanks,

Mark

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-11 : 12:57:32

quote:
Originally posted by mrpush

Ok,

So I figure if I take the data in this odd table that will not sort correctly, and I insert it into another table USING an ORDER BY, it should create the new table in the correct sort order, right?

No, it does not!

I understand this whole "relational DB's don't physically sort tables" but they actually DO.

Is there a way for me to get this one row physically in a table at the position I want (the top spot)?

Thanks,

Mark

We didnt tell they wont but point is it cant be guaranteed always as it depends on lot of factors like presence of clustered index etc. thats why only thing thats reliable is to use an explicit ORDER BY in your select and it will always bring you results in order you specified

Again physical position of row inside a table is irrelevant as you can always bring it in order you want using select with order by

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-03-11 : 13:53:48

As has already been mentioned there is no guaranteed order in a table. The ONLY way to guarantee the order of your results is to specify an ORDER BY clause. Even specifing an ORDER BY on an insert may not work the way you would like if you are using an IDENTITY column (http://support.microsoft.com/default.aspx?scid=kb;en-us;273586)

I see that you are using an ORDE BY clasue for inserting, but what is issue you are facing? Is one of the tables you are trying to apply order to not working the way you want it to work? If so, can you state your queston in a concise manor with sample data and expected output?

http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2013-03-11 : 20:44:10

I'd review your design and your assumptions on how it all works.
1) Order by works on the selects, not inserts. Once inserted, the order of insertion means nothing and effectively does not exist. You need to convince yourself of this. It's the single fact that is causing your frustration.
2) Alter table as an operational, erm..., operation is always bad. It's not multi user friendly, it's just plain wrong. Your schema needs to be static for it to be relationally correct. (Temp tables aside, but don't get me started on how those are unnecessary and overused in SQL Server Land!)
3) What I think you want is a header row on top of sorted data. This is best achieved by adding the header at the client and sorting the data in the database:

select emp_number, emp_name, emp_addr, emp_city, emp_state, Emp_zip, year, qtr, SumEFed, SumLW, PSD_Code
from mydatatable
where emp_branch = 5
ORDER BY emp_number

Then use the client to output the header and read the query outputting each line.

If you really, really insist on doing it all in the database you will have to jump through a few hoops as your header row (varchar) will sort differently to the data rows.
Here's an example using a different table to yours (so I could test it) but you should be able to work it out from here.
select
*
,ROW_NUMBER() over(order by table_name) rn
from
INFORMATION_SCHEMA.TABLES
union all
select
'table cat','schema','tab name','tab_type',0
order by rn

mrpush
Starting Member

14 Posts

Posted - 2013-03-12 : 10:08:01

Hi,

First thanks all for you help, much appreciated.

Let me do a quick recap of my entire setup.

-I generate 3 tables from a single source table w:

select emp_number, emp_name, emp_addr, emp_city, emp_state, Emp_zip, year, qtr, SumEFed, SumLW, PSD_Code
into mytable
from datatable
where emp_branch = 2
ORDER BY 2

-I add a HEADER ROW to the tables with an Insert command:


insert into mytable
values ('E', YEAR(GETDATE()), DATENAME(quarter, GETDATE()), '123456789', '0', 'comp', 'address', 'city', 'state', 'zip', '123456')

-to get this to work, I have to use some ALTER TABLE ALTER COLUMN commands to avoid SQL errors on wrong format.

-I take the resulting tables, and I export them to CSV file. The insert header row is needed and must be the first record.

-I need to automate this process so it can be run from a workstation and I will use RUNDTS command line (with 2 date parameters) to run a DTS package that fires a STORED PROCEDURE.

Now I have come to understand and I will not FIGHT SQL anymore on physical table sorting, however, SQL does not randomly insert rows as everyone says, it puts my header row in one of two places every time, so it makes a choice and I just believe I should be able to tell it what choice it makes.

I'm beyond this now, thanks, however when using an ORDER BY when creating the table, I would think it would order the physical table records as it was told, but it does not do it correctly. I can only get my header row up top if i use selects and order by.

I'm not sure exactly what you mean by the "CLIENT". I really need this automated and able to run from users workstations and if I can;t get that header row up top every time, it kills the automation process.

Thanks much,

Mark

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-12 : 13:40:14

you can always get header on top using below approach. Add one more column to your table say OrderCol. then make insert as below

insert into mytable
values ('E', YEAR(GETDATE()), DATENAME(quarter, GETDATE()), '123456789', '0', 'comp', 'address', 'city', 'state', 'zip', '123456',0)

select emp_number, emp_name, emp_addr, emp_city, emp_state, Emp_zip, year, qtr, SumEFed, SumLW, PSD_Code,CAST(1 AS int) AS OrderCol
into mytable
from datatable
where emp_branch = 2
ORDER BY 2

Now just do ORDER BY OrderCol in CSV population step and you'll get HEADER always first followed by your data

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2013-03-12 : 19:57:12

quote:
Originally posted by mrpush

I'm not sure exactly what you mean by the "CLIENT". I really need this automated and able to run from users workstations and if I can;t get that header row up top every time, it kills the automation process.

Client=Thing that invokes SQL.
That can be a batch job, web application, fat client, report, whatever. In your case a batch process of some sort.

I'm not sure where you ended up, so to recap myself:
- You do not necessarily need to insert all the data into a table first as my example showed.
- If you still feel you have to (and you don't), use a temp table
- Do NOT alter the table structure to do any of this.

Here's how to do it:
- Create a select statment for your data (3 tables unioned if that's how it is) and and also add the row_number order by as in my example.
- Union THAT with your header row and give the header row a row number of 0 (also as in my example).
- Select THAT and add ORDER BY.
- You might have to cast your data to varchar (after sorting) to allow the union with the header to occur. Do it after though so you can still sort properly inside your main query.

select * from -- might have to select columns individually and cast
(
select *, row_number() over (order by emp_number) rn from
(
select * from tab1 union all
select * from tab2 union all
select * from tab3
) x
union all
select 'header 1', 'header 2', 'header 3',....,0 as rn
) y
order by rn

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources