Order By is ignored on Insert Into statement

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)

Order By is ignored on Insert Into statement

Author

Topic

umertahir
Posting Yak Master

154 Posts

Posted - 2008-08-27 : 04:34:20

Hi,

I'm doing INSERT INTO a table, which has clustered unique index on it, but somehow it ignores the ORDER BY clause in my syntax.
What i'm doing is as follows:

1) Dropping a table if it exists

2) Creating a table:


CREATE TABLE [dbo].[tblOutputBroodstockSortV4](
	[Programme] [varchar](50) NULL,
	[Site] [varchar](50) NULL,
	[YearGroup] [int] NULL,
	[MaleParent] [char](17) NULL,
	[FemaleParent] [char](17) NULL,
	[Family] [varchar](50) NOT NULL,
	[Blupid] [char](17) NULL,
	[PitTag] [varchar](50) NULL,
	[Clipdate] [int] NULL,
	[SampleNumber] [varchar](50) NULL,
	[sex] [varchar](50) NOT NULL,
	[newloc] [varchar](50) NULL,
	[sudate] [int] NULL,
	[sutankto] [char](10) NULL
) ON [PRIMARY]

3) Creating a unique clustered index on a column to eliminate duplicates:


CREATE UNIQUE CLUSTERED INDEX [idxBlupIDUnique] ON [dbo].[tblOutputBroodstockSortV4] 
(
	[Blupid] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = ON, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

4) Inserting record into the table:


INSERT INTO [dbo].[tblOutputBroodstockSortV4]
SELECT *
FROM [dbo].[tblOutputBroodstockSort]
ORDER BY newloc DESC

Now if i run the SELECT statement seperately the ORDER BY clause works but when I'm using it here in INSERT INTO it somehow loses its ORDER BY arrangement. Any suggestions will be appreciated.

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-27 : 04:39:31

quote:
it somehow loses its ORDER BY arrangement

What do you mean it by this ? And how do you know the ORDER BY is not working ?

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-27 : 04:41:08

Why do you think it ignored order by can you try putting an ID column in your table and see if order of insertion was based on column in order by?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-27 : 04:41:55

just a bit to add "an ID column with identity(1,1)"

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-27 : 04:49:28

Try like below and see if its correctly inserted as per your order

CREATE TABLE [dbo].[tblOutputBroodstockSortV4](
        [Seq] int identity(1,1),
	[Programme] [varchar](50) NULL,
	[Site] [varchar](50) NULL,
	[YearGroup] [int] NULL,
	[MaleParent] [char](17) NULL,
	[FemaleParent] [char](17) NULL,
	[Family] [varchar](50) NOT NULL,
	[Blupid] [char](17) NULL,
	[PitTag] [varchar](50) NULL,
	[Clipdate] [int] NULL,
	[SampleNumber] [varchar](50) NULL,
	[sex] [varchar](50) NOT NULL,
	[newloc] [varchar](50) NULL,
	[sudate] [int] NULL,
	[sutankto] [char](10) NULL
) ON [PRIMARY]




CREATE UNIQUE CLUSTERED INDEX [idxBlupIDUnique] ON [dbo].[tblOutputBroodstockSortV4] 
(
	[Blupid] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = ON, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]




INSERT INTO [dbo].[tblOutputBroodstockSortV4](all columns except Seq)
SELECT *
FROM [dbo].[tblOutputBroodstockSort]
ORDER BY newloc DESC

SELECT * FROM tblOutputBroodstockSortV4 ORDER BY Seq

umertahir
Posting Yak Master

154 Posts

Posted - 2008-08-27 : 04:56:47

quote:
Originally posted by khtan

quote:
it somehow loses its ORDER BY arrangement

What do you mean it by this ? And how do you know the ORDER BY is not working ?

KH
[spoiler]Time is always against us[/spoiler]

The result of SELECT statement on its own is:
Programme Site YearGroup MaleParent FemaleParent Family Blupid PitTag newloc sutankto
Scottish Ormsary 2004 00Or_L2M20242 00Or_L2F21941 strBP_Or04_0180 04Or_13435 0006229E69 D21 D17
Scottish Ormsary 2004 00Or_L2M20242 00Or_L2F21941 strBP_Or04_0180 04Or_13435 0006229E69 D21 D17
Scottish Ormsary 2004 00Or_L2M20242 00Or_L2F21941 strBP_Or04_0180 04Or_13435 0006229E69 D21 D17
Scottish Ormsary 2004 00Or_L2M20242 00Or_L2F21941 strBP_Or04_0180 04Or_13435 0006229E69 D21 D17
Scottish Ormsary 2004 00Or_L2M20242 00Or_L2F21941 strBP_Or04_0180 04Or_13435 0006229E69 NULL D17
Scottish Ormsary 2004 00Or_L2M20242 00Or_L2F21941 strBP_Or04_0180 04Or_13435 0006229E69 NULL D17
Scottish Ormsary 2004 00Or_L2M20242 00Or_L2F21941 strBP_Or04_0180 04Or_13435 0006229E69 NULL D17
Scottish Ormsary 2004 00Or_L2M20242 00Or_L2F21941 strBP_Or04_0180 04Or_13435 0006229E69 NULL D17

and the result from the INSERT INTO statement is:
Programme Site YearGroup MaleParent FemaleParent Family Blupid PitTag newloc sutankto
Scottish Ormsary 2004 00Or_L2M20242 00Or_L2F21941 strBP_Or04_0180 04Or_13435 0006229E69 NULL D17

umertahir
Posting Yak Master

154 Posts

Posted - 2008-08-27 : 05:10:31

quote:

Originally posted by visakh16

Try like below and see if its correctly inserted as per your order

CREATE TABLE [dbo].[tblOutputBroodstockSortV4](
        [Seq] int identity(1,1),
	[Programme] [varchar](50) NULL,
	[Site] [varchar](50) NULL,
	[YearGroup] [int] NULL,
	[MaleParent] [char](17) NULL,
	[FemaleParent] [char](17) NULL,
	[Family] [varchar](50) NOT NULL,
	[Blupid] [char](17) NULL,
	[PitTag] [varchar](50) NULL,
	[Clipdate] [int] NULL,
	[SampleNumber] [varchar](50) NULL,
	[sex] [varchar](50) NOT NULL,
	[newloc] [varchar](50) NULL,
	[sudate] [int] NULL,
	[sutankto] [char](10) NULL
) ON [PRIMARY]




CREATE UNIQUE CLUSTERED INDEX [idxBlupIDUnique] ON [dbo].[tblOutputBroodstockSortV4] 
(
	[Blupid] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = ON, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]




INSERT INTO [dbo].[tblOutputBroodstockSortV4](all columns except Seq)
SELECT *
FROM [dbo].[tblOutputBroodstockSort]
ORDER BY newloc DESC

SELECT * FROM tblOutputBroodstockSortV4 ORDER BY Seq

No affect of having identity column in it, it still is bring the wrong row at the top.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-27 : 05:14:42

quote:
No affect of having identity column in it, it still is bring the wrong row at the top.

Records are not stored in the table in any particular order. You determine the sequence or ordering of the record you want when you retrieve the record using SELECT . .. ORDER BY.


select *
from   tblOutputBroodstockSortV4
order by newloc DESC

KH
[spoiler]Time is always against us[/spoiler]

umertahir
Posting Yak Master

154 Posts

Posted - 2008-08-27 : 05:17:52

quote:
Originally posted by khtan

quote:
No affect of having identity column in it, it still is bring the wrong row at the top.

Records are not stored in the table in any particular order. You determine the sequence or ordering of the record you want when you retrieve the record using SELECT . .. ORDER BY.

select *
from tblOutputBroodstockSortV4
order by newloc DESC

KH
[spoiler]Time is always against us[/spoiler]

The whole point of getting UNIQUE CLUSTERED INDEX on the table is that it will hold only one record per id, so doing ORDER BY on that table is of no use.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-27 : 05:20:50

quote:

Originally posted by umertahir

quote:

Originally posted by visakh16

Try like below and see if its correctly inserted as per your order

CREATE TABLE [dbo].[tblOutputBroodstockSortV4](
        [Seq] int identity(1,1),
	[Programme] [varchar](50) NULL,
	[Site] [varchar](50) NULL,
	[YearGroup] [int] NULL,
	[MaleParent] [char](17) NULL,
	[FemaleParent] [char](17) NULL,
	[Family] [varchar](50) NOT NULL,
	[Blupid] [char](17) NULL,
	[PitTag] [varchar](50) NULL,
	[Clipdate] [int] NULL,
	[SampleNumber] [varchar](50) NULL,
	[sex] [varchar](50) NOT NULL,
	[newloc] [varchar](50) NULL,
	[sudate] [int] NULL,
	[sutankto] [char](10) NULL
) ON [PRIMARY]




CREATE UNIQUE CLUSTERED INDEX [idxBlupIDUnique] ON [dbo].[tblOutputBroodstockSortV4] 
(
	[Blupid] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = ON, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]




INSERT INTO [dbo].[tblOutputBroodstockSortV4](all columns except Seq)
SELECT *
FROM [dbo].[tblOutputBroodstockSort]
ORDER BY newloc DESC

SELECT * FROM tblOutputBroodstockSortV4 ORDER BY Seq

No affect of having identity column in it, it still is bring the wrong row at the top.

did you try retrieving record according to order of new id column? was it as per your required order of newloc column?

umertahir
Posting Yak Master

154 Posts

Posted - 2008-08-27 : 05:26:29

quote:

Originally posted by visakh16

quote:

Originally posted by umertahir

quote:

Originally posted by visakh16

Try like below and see if its correctly inserted as per your order

CREATE TABLE [dbo].[tblOutputBroodstockSortV4](
        [Seq] int identity(1,1),
	[Programme] [varchar](50) NULL,
	[Site] [varchar](50) NULL,
	[YearGroup] [int] NULL,
	[MaleParent] [char](17) NULL,
	[FemaleParent] [char](17) NULL,
	[Family] [varchar](50) NOT NULL,
	[Blupid] [char](17) NULL,
	[PitTag] [varchar](50) NULL,
	[Clipdate] [int] NULL,
	[SampleNumber] [varchar](50) NULL,
	[sex] [varchar](50) NOT NULL,
	[newloc] [varchar](50) NULL,
	[sudate] [int] NULL,
	[sutankto] [char](10) NULL
) ON [PRIMARY]




CREATE UNIQUE CLUSTERED INDEX [idxBlupIDUnique] ON [dbo].[tblOutputBroodstockSortV4] 
(
	[Blupid] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = ON, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]




INSERT INTO [dbo].[tblOutputBroodstockSortV4](all columns except Seq)
SELECT *
FROM [dbo].[tblOutputBroodstockSort]
ORDER BY newloc DESC

SELECT * FROM tblOutputBroodstockSortV4 ORDER BY Seq

No affect of having identity column in it, it still is bring the wrong row at the top.

did you try retrieving record according to order of new id column? was it as per your required order of newloc column?

The whole purpose of doing CLUSTERED INDEX is so that I can get prioritise the good record, preferably no NULL, in the top row which will be picked by the INSERT INTO statement and rest of the rows will be ignored and they won't be inserted into the final table.
So retrieving the records using ORDER BY will have no affect as the final table has a single record per id anyway.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-27 : 05:34:58

But you can guarantee the order of data retrieval from a table unless you use an ORDER BY and specify it explicitly

umertahir
Posting Yak Master

154 Posts

Posted - 2008-08-27 : 05:47:52

quote:
Originally posted by visakh16

But you can guarantee the order of data retrieval from a table unless you use an ORDER BY and specify it explicitly

Retrieving the data out from the final table was never a question here because there will be ONLY ONE ROW PER ID.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-27 : 06:05:47

Whether its final table or some other table, my point is that you cant guarantee order of data retrieval unless you specify it in your select statement by means of an order by even if it has a clustered index.

umertahir
Posting Yak Master

154 Posts

Posted - 2008-08-27 : 06:35:06

quote:
Originally posted by visakh16

Whether its final table or some other table, my point is that you cant guarantee order of data retrieval unless you specify it in your select statement by means of an order by even if it has a clustered index.

Then why you think that ORDER BY hasn't worked in the INSERT statement however it works fine while retrieving the data?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-27 : 06:48:43

quote:
Originally posted by umertahir

quote:
Originally posted by visakh16

Whether its final table or some other table, my point is that you cant guarantee order of data retrieval unless you specify it in your select statement by means of an order by even if it has a clustered index.

Then why you think that ORDER BY hasn't worked in the INSERT statement however it works fine while retrieving the data?

It has worked in insert statement. Only evidence to find out that has worked is by selecting records from final table based on increasing order of genrated identity value and checking if records are coming as per decreasing order of your newcol value (which is what you specified in ORDER BY during insert)

umertahir
Posting Yak Master

154 Posts

Posted - 2008-08-27 : 07:03:17

quote:
Originally posted by visakh16

quote:
Originally posted by umertahir

quote:
Originally posted by visakh16

Whether its final table or some other table, my point is that you cant guarantee order of data retrieval unless you specify it in your select statement by means of an order by even if it has a clustered index.

Then why you think that ORDER BY hasn't worked in the INSERT statement however it works fine while retrieving the data?

It has worked in insert statement. Only evidence to find out that has worked is by selecting records from final table based on increasing order of genrated identity value and checking if records are coming as per decreasing order of your newcol value (which is what you specified in ORDER BY during insert)

As I have IGNORE_DUP_KEY = ON so it has inserted only one record per id, means that the NULL record from newloc has been picked up, sorry to repeat it over n over again.
Now for your statisfaction I have retrieved the data using ORDER BY in my select statement, because there is only one record per id in that table so ORDER BY is not coming into use and it has fetched me same old record in which newloc is NULL. Hope that has cleared my point.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-27 : 08:02:36

Ah i think i got you. i missed the IGNORED DUP part. Then why dant you filter it while inserting itself?

umertahir
Posting Yak Master

154 Posts

Posted - 2008-08-27 : 09:29:51

quote:
Originally posted by visakh16

Ah i think i got you. i missed the IGNORED DUP part. Then why dant you filter it while inserting itself?

How?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-27 : 10:01:54

INSERT INTO [dbo].[tblOutputBroodstockSortV4](all columns except Seq)
SELECT *
FROM [dbo].[tblOutputBroodstockSort]
WHERE newloc IS NOT NULL
ORDER BY newloc DESC

umertahir
Posting Yak Master

154 Posts

Posted - 2008-08-27 : 10:26:14

quote:
Originally posted by visakh16

INSERT INTO [dbo].[tblOutputBroodstockSortV4](all columns except Seq)
SELECT *
FROM [dbo].[tblOutputBroodstockSort]
WHERE newloc IS NOT NULL
ORDER BY newloc DESC

The reason why I do not want to have WHERE clause is because not all of the rows have a data in newloc, so I the preferred row is the one having a data in the column and if there is no row which has data in it then get the one with NULL.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources