Record Sorting Problem

Question

Record Sorting Problem

wwei

SSC Eights!

Points: 961
More actions
March 11, 2010 at 8:04 pm

#212374

Hi All,
For some reason, I need generate a table from an existing table, order by another columns. But I couldn't sort it.
For example, I have a table called 'equip', it shows:
equip_id equipnum paid
1 52233 1
2 23344 0
3 AB200 1
4 44200 1
I want remove the un-paid records, sort it by equipnum, and put the results into a new table, it should like:
equip_id equipnum paid
4 44200 1
1 52233 1
3 AB200 1
I use following code
SELECT * INTO equip_output FROM equip WHERE 1=0
ALTER TABLE equip_output ADD PRIMARY KEY (equip_id)
SET IDENTITY_INSERT equip_output ON
INSERT INTO equip_output (equip_id , equipnum, paid )
SELECT equip_id , equipnum, paid
FROM equip
WHERE paid = 1
ORDER BY equipnum
SET IDENTITY_INSERT equip_output OFF
but, I got following results when I try 'SELECT * FROM equip_output'
equip_id equipnum paid
1 52233 1
3 AB200 1
4 44200 1
Appreciate if someone could help me. Thanks in advance!

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

'SELECT * FROM equip_output'

The only way to guarantee the order of a select is to use an ORDER BY. The fact that a table sometimes seems to be in the correct order is not (as you're finding out) any kind of a guarantee as to order.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ron McCullough SSC Guru Points: 63877 More actions · Answer 2

As per Jeff change your final select statement to:

SELECT * FROM equip_output ORDER BY equipnum

--giving you this output

equip_idequipnumpaid

4 44200 1

1 52233 1

3 AB200 1

Or

SELECT * FROM equip_output ORDER BY equipnum

--Yielding:

equip_idequipnumpaid

1 52233 1

3 AB200 1

4 44200 1

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

wwei SSC Eights! Points: 961 More actions · Answer 3

wwei

SSC Eights!

Points: 961

March 13, 2010 at 7:28 am

#1133205

Hi Jeff & Ron, Thank you for your helps!

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

Thanks for the feedback, wwei.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ron McCullough SSC Guru Points: 63877 More actions · Answer 5

wwei

Thanks, if you have other questions - come on back.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

wwei SSC Eights! Points: 961 More actions · Answer 6

Hi Jeff & Ron,

Thanks again!

I'm working on updating part of an old project. Let the output table sorted without the use of 'order by' is the requirement. (If use 'order by', means other part code that gets data from this table should be updated as well. At this time, they don't plan to update it)

the ways I can let it sorted are: don't keep the original identity(the equip_id column on this example), or don't set primary key on the output table.

Ron McCullough SSC Guru Points: 63877 More actions · Answer 7

wwei
the ways I can let it sorted are: don't keep the original identity(the equip_id column on this example), or don't set primary key on the output table

Do not believe that the above will guarantee that the data will be presented in a sorted order. But since you are extremely limited in what you can do, that is, not modify procedures that select data from this table, gotta do what you gotta do. Good luck

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Paul White SSC Guru Points: 150468 More actions · Answer 8

Actually, there is a way to to reliably get a particular ordering without an ORDER BY.

Records inserted into a HEAP will come back in insertion order, as long as no DELETE or UPDATE operations are performed.

To be clear though, this is an implementation detail, and relies on the way current versions (2005 and 2008) are coded internally.

It is not officially documented, and the behaviour could change in future hot fixes, service packs, and versions.

If you really have no alternative (and I am still not exactly clear why that should be), the following will work:

USE tempdb;

GO

CREATE TABLE dbo.Equip

(

equip_id INTEGER IDENTITY(1,1) PRIMARY KEY,

equipnum NVARCHAR(20) NOT NULL,

paid BIT NOT NULL

);

GO

INSERT Equip (equipnum, paid)

VALUES ('52233', 1),

('23344', 0),

('AB200', 1),

('44200', 1);

GO

SELECT equip_id,

equipnum,

paid

INTO dbo.Equip_output

FROM equip

WHERE paid = 1

ORDER BY

equipnum ASC;

SELECT * FROM dbo.Equip;

SELECT * FROM dbo.Equip_output

DROP TABLE equip_output;

DROP TABLE equip;

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

wwei SSC Eights! Points: 961 More actions · Answer 9

Hi Ron & Paul,

Thank you for your posts and queries. That would help. Particularly, the 'Records inserted into a HEAP will come back in insertion order...' lets me understand it better.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 10

wwei (3/14/2010)
Hi Ron & Paul,
Thank you for your posts and queries. That would help. Particularly, the 'Records inserted into a HEAP will come back in insertion order...' lets me understand it better.

Along with the other things that Paul cited that will break this, it will also break if parallelism takes place or (obviously) if more than one INSERT query is working at the same time. Be very, very careful. Although there is some good utility in the method (especially for temp tables), most folks will tell you that it's a form of "Death by SQL" especially since the method DIDN'T work in SQL Server 2000. It would be MUCH safer to have an IDENTITY column on the table and sort on that IDENTITY column if you want to come closer to a guaranteed order.

At the very least, use TABLOCKX and MAXDOP 1 during INSERTS (whether there's an IDENTITY column or not) to at least give the method a shot at preserving the order of input. Still, "self ordered" heaps are NOT like the "Quirky Update" which has worked since the beginning of time... "self ordered" heaps are a manifestation of some changes they made to the optimizer in 2k5.

As a side bar, add it to your list of things to verify if you're one of those folks that install's CU's and Hot Fixes on a regular basis. For SP's, you should, of course, always do full regression testing on a test box before installing it on a production box.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Paul White SSC Guru Points: 150468 More actions · Answer 11

Jeff,

You're quite right. In many ways, I wish I had resisted the temptation to post the Heap insert thing. The correct thing to do here would be to re-architect to allow explicit an ORDER BY every time a guaranteed order is required. Anything else is just asking for heaps of bother further down the track.

At least I didn't mention that SELECT...INTO with an ORDER BY is guaranteed to be ordered...:w00t:

Paul

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

wwei SSC Eights! Points: 961 More actions · Answer 12

Hi All,

thank you for all your replies and examinations. For now, I'll try to use an IDENTITY column to get closer to a guaranteed order.

All your posts are good points for me to start to learn something, thanks again!

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 13

Paul White (3/14/2010)
Jeff,
You're quite right. In many ways, I wish I had resisted the temptation to post the Heap insert thing. The correct thing to do here would be to re-architect to allow explicit an ORDER BY every time a guaranteed order is required. Anything else is just asking for heaps of bother further down the track.
At least I didn't mention that SELECT...INTO with an ORDER BY is guaranteed to be ordered...:w00t:
Paul

Heh... I know what you mean. There are a lot of very useful nuances (that I'm also guilty of using on occasion) that are only "guaranteed" to work if it's Tuesday, it's raining on the next block but not this block, and a certain colored walking catfish is crossing an East bound lane wth double yellow lines in a Northerly fashion. 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Paul White SSC Guru Points: 150468 More actions · Answer 14

Jeff Moden (3/14/2010)
...only...if it's Tuesday, it's raining on the next block but not this block, and a certain colored walking catfish is crossing an East bound lane wth double yellow lines in a Northerly fashion. 😛

Your mind must be a strange place to live in, Mr Moden! :laugh:

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi