I'm selecting records from a table and inserting them into another existing table
Use DSRBQ000
INSERT INTO dbo.db_table_information
Select Table_Name, Column_Name + ' ' + Upper(data_type) +
CASE WHEN data_type IN('binary','char','nchar','nvarchar','varbinary', 'varchar') THEN '('
+ Cast(character_maximum_length AS varchar(10))+')'
WHEN data_type IN('decimal','numeric') THEN '(' + Cast(numeric_precision as varchar(3)) + ','
+ Cast(numeric_scale as varchar(3))+ ')'
Else ''
End +
CASE WHEN columnproperty(object_id(table_name),column_name,' IsIdentity')= 1 THEN ' IDENTITY' +
'(' + Cast(ident_seed(table_name) AS varchar(10)) + ',' + Cast(ident_incr(table_name) AS varchar(10)) + ')'
Else ''
End +
CASE WHEN is_nullable = 'YES' THEN ' NULL'
ELSE ''
END 'Column_Definition', ordinal_position
from information_schema.columns
where table_name IN(select distinct table_name from information_schema.tables where table_type = 'BASE TABLE')
and table_name NOT IN('dtproperties','dbo.db_table_information')
order by table_name, ordinal_position
I'm trying to first order by table_name and then ordinal_position. However, when viewing the table that it data is getting inserted into, I notice that even though it is ordered by table_name, sometimes a row is out of order according to ordinal position.
Is there a valid reason for this? Are you not allow to select the order from which a recordset gets inserted into a table? If thats the case, how can I update the db_table_information and save it so it is in table_name, ordinal_position order?Inserting a sorted record set is usally a wast of time as there is most likely an index in place. When you issue a select your result set will be based on the clustered index or first non-clustered index created for the table.
If you always want db_table_information to be in table_name, ordinal_position order then create an index on those attributes.|||figured out why this occured.. i defined ordinal position as char instead of a number.|||DOH! If only these computers would do as we want rather than do as we ask the world would be a better place!
Showing posts with label dbo. Show all posts
Showing posts with label dbo. Show all posts
Friday, March 30, 2012
Monday, March 26, 2012
ORDER BY in UNION
I cannot make the result set ORDER BY LastName and FirstName:
CREATE VIEW dbo.viewApp_Web_ContactDetails
AS
SELECT TOP 100 PERCENT dbo.tblApp_Contact.Salutation,
dbo.tblApp_Contact.FirstName,
dbo.tblApp_Contact.LastName
FROM dbo.tblApp_Contact
ORDER BY tblApp_Contact.LastName, tblApp_Contact.FirstName
UNION ALL
SELECT TOP 100 PERCENT dbo.tblApp_WebContact.Salutation,
dbo.tblApp_WebContact.FirstName,
dbo.tblApp_WebContact.LastName
FROM dbo.tblApp_WebContact
ORDER BY tblApp_WebContact.LastName, tblApp_WebContact.FirstName
How do I make the result set in the order by LastName and then FirstName ?Man Utd (alanpltseNOSPAM@.yahoo.com.au) writes:
> I cannot make the result set ORDER BY LastName and FirstName:
> CREATE VIEW dbo.viewApp_Web_ContactDetails
> AS
> SELECT TOP 100 PERCENT dbo.tblApp_Contact.Salutation,
> dbo.tblApp_Contact.FirstName,
> dbo.tblApp_Contact.LastName
> FROM dbo.tblApp_Contact
> ORDER BY tblApp_Contact.LastName, tblApp_Contact.FirstName
> UNION ALL
> SELECT TOP 100 PERCENT dbo.tblApp_WebContact.Salutation,
> dbo.tblApp_WebContact.FirstName,
> dbo.tblApp_WebContact.LastName
> FROM dbo.tblApp_WebContact
> ORDER BY tblApp_WebContact.LastName, tblApp_WebContact.FirstName
> How do I make the result set in the order by LastName and then FirstName ?
Remove the first ORDER BY clause. ORDER BY applies to the entire SELECT
statement, not the various parts in a UNION.
Then again, it's not really meaningful to have ORDER BY in a view. You
should always use ORDER BY when you select data. If you say "SELECT * FROM
myview", without ORDER BY, you can get data back in any order.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi Man,
This might help you
CREATE VIEW dbo.viewApp_Web_ContactDetails
AS
SELECT Salutation, FirstName, LastName
FROM(
SELECT dbo.tblApp_Contact.Salutation,
dbo.tblApp_Contact.FirstName,
dbo.tblApp_Contact.LastName
FROM dbo.tblApp_Contact
UNION ALL
SELECT dbo.tblApp_WebContact.Salutation,
dbo.tblApp_WebContact.FirstName,
dbo.tblApp_WebContact.LastName
FROM dbo.tblApp_WebContact
) TEMP_TAB
ORDER BY LastName, FirstName
Please let me know if you have any questions
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Man Utd" wrote:
> I cannot make the result set ORDER BY LastName and FirstName:
> CREATE VIEW dbo.viewApp_Web_ContactDetails
> AS
> SELECT TOP 100 PERCENT dbo.tblApp_Contact.Salutation,
> dbo.tblApp_Contact.FirstName,
> dbo.tblApp_Contact.LastName
> FROM dbo.tblApp_Contact
> ORDER BY tblApp_Contact.LastName, tblApp_Contact.FirstName
> UNION ALL
> SELECT TOP 100 PERCENT dbo.tblApp_WebContact.Salutation,
> dbo.tblApp_WebContact.FirstName,
> dbo.tblApp_WebContact.LastName
> FROM dbo.tblApp_WebContact
> ORDER BY tblApp_WebContact.LastName, tblApp_WebContact.FirstName
> How do I make the result set in the order by LastName and then FirstName ?
>
>|||There's just one thing missing: TOP must be specified in the SELECT statemen
t
of a view if ORDER BY is specified.
Also, the use of a derived table is not really needed.
CREATE VIEW dbo.viewApp_Web_ContactDetails
AS
SELECT TOP 100 PERCENT dbo.tblApp_Contact.Salutation,
dbo.tblApp_Contact.FirstName,
dbo.tblApp_Contact.LastName
FROM dbo.tblApp_Contact
UNION ALL
SELECT TOP 100 PERCENT dbo.tblApp_WebContact.Salutation,
dbo.tblApp_WebContact.FirstName,
dbo.tblApp_WebContact.LastName
FROM dbo.tblApp_WebContact
ORDER BY tblApp_WebContact.LastName, tblApp_WebContact.FirstName
As Erland already mentioned.
ML|||"ML" <ML@.discussions.microsoft.com> wrote in message
news:B8F58855-C6D2-4D7E-B02A-F606A3B1A2E3@.microsoft.com...
> There's just one thing missing: TOP must be specified in the SELECT
> statement
> of a view if ORDER BY is specified.
> SELECT TOP 100 PERCENT
Well I'm not sure about the OP, but it's answered my question.
Thanks|||So, I guess my mother was right - sometimes I answer questions before they
were asked. :)
ML|||In this view definition, the ORDER BY clause has no function, because it
does not change the resultset (since you want to select 100 PERCENT).
And since a view has no implicit order, an ORDER BY clause for sorting
purposes does not work.
If you want a specific order, then you must specify an ORDER BY clause
when you select from the view.
Gert-Jan
Man Utd wrote:
> I cannot make the result set ORDER BY LastName and FirstName:
> CREATE VIEW dbo.viewApp_Web_ContactDetails
> AS
> SELECT TOP 100 PERCENT dbo.tblApp_Contact.Salutation,
> dbo.tblApp_Contact.FirstName,
> dbo.tblApp_Contact.LastName
> FROM dbo.tblApp_Contact
> ORDER BY tblApp_Contact.LastName, tblApp_Contact.FirstName
> UNION ALL
> SELECT TOP 100 PERCENT dbo.tblApp_WebContact.Salutation,
> dbo.tblApp_WebContact.FirstName,
> dbo.tblApp_WebContact.LastName
> FROM dbo.tblApp_WebContact
> ORDER BY tblApp_WebContact.LastName, tblApp_WebContact.FirstName
> How do I make the result set in the order by LastName and then FirstName ?
CREATE VIEW dbo.viewApp_Web_ContactDetails
AS
SELECT TOP 100 PERCENT dbo.tblApp_Contact.Salutation,
dbo.tblApp_Contact.FirstName,
dbo.tblApp_Contact.LastName
FROM dbo.tblApp_Contact
ORDER BY tblApp_Contact.LastName, tblApp_Contact.FirstName
UNION ALL
SELECT TOP 100 PERCENT dbo.tblApp_WebContact.Salutation,
dbo.tblApp_WebContact.FirstName,
dbo.tblApp_WebContact.LastName
FROM dbo.tblApp_WebContact
ORDER BY tblApp_WebContact.LastName, tblApp_WebContact.FirstName
How do I make the result set in the order by LastName and then FirstName ?Man Utd (alanpltseNOSPAM@.yahoo.com.au) writes:
> I cannot make the result set ORDER BY LastName and FirstName:
> CREATE VIEW dbo.viewApp_Web_ContactDetails
> AS
> SELECT TOP 100 PERCENT dbo.tblApp_Contact.Salutation,
> dbo.tblApp_Contact.FirstName,
> dbo.tblApp_Contact.LastName
> FROM dbo.tblApp_Contact
> ORDER BY tblApp_Contact.LastName, tblApp_Contact.FirstName
> UNION ALL
> SELECT TOP 100 PERCENT dbo.tblApp_WebContact.Salutation,
> dbo.tblApp_WebContact.FirstName,
> dbo.tblApp_WebContact.LastName
> FROM dbo.tblApp_WebContact
> ORDER BY tblApp_WebContact.LastName, tblApp_WebContact.FirstName
> How do I make the result set in the order by LastName and then FirstName ?
Remove the first ORDER BY clause. ORDER BY applies to the entire SELECT
statement, not the various parts in a UNION.
Then again, it's not really meaningful to have ORDER BY in a view. You
should always use ORDER BY when you select data. If you say "SELECT * FROM
myview", without ORDER BY, you can get data back in any order.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi Man,
This might help you
CREATE VIEW dbo.viewApp_Web_ContactDetails
AS
SELECT Salutation, FirstName, LastName
FROM(
SELECT dbo.tblApp_Contact.Salutation,
dbo.tblApp_Contact.FirstName,
dbo.tblApp_Contact.LastName
FROM dbo.tblApp_Contact
UNION ALL
SELECT dbo.tblApp_WebContact.Salutation,
dbo.tblApp_WebContact.FirstName,
dbo.tblApp_WebContact.LastName
FROM dbo.tblApp_WebContact
) TEMP_TAB
ORDER BY LastName, FirstName
Please let me know if you have any questions
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Man Utd" wrote:
> I cannot make the result set ORDER BY LastName and FirstName:
> CREATE VIEW dbo.viewApp_Web_ContactDetails
> AS
> SELECT TOP 100 PERCENT dbo.tblApp_Contact.Salutation,
> dbo.tblApp_Contact.FirstName,
> dbo.tblApp_Contact.LastName
> FROM dbo.tblApp_Contact
> ORDER BY tblApp_Contact.LastName, tblApp_Contact.FirstName
> UNION ALL
> SELECT TOP 100 PERCENT dbo.tblApp_WebContact.Salutation,
> dbo.tblApp_WebContact.FirstName,
> dbo.tblApp_WebContact.LastName
> FROM dbo.tblApp_WebContact
> ORDER BY tblApp_WebContact.LastName, tblApp_WebContact.FirstName
> How do I make the result set in the order by LastName and then FirstName ?
>
>|||There's just one thing missing: TOP must be specified in the SELECT statemen
t
of a view if ORDER BY is specified.
Also, the use of a derived table is not really needed.
CREATE VIEW dbo.viewApp_Web_ContactDetails
AS
SELECT TOP 100 PERCENT dbo.tblApp_Contact.Salutation,
dbo.tblApp_Contact.FirstName,
dbo.tblApp_Contact.LastName
FROM dbo.tblApp_Contact
UNION ALL
SELECT TOP 100 PERCENT dbo.tblApp_WebContact.Salutation,
dbo.tblApp_WebContact.FirstName,
dbo.tblApp_WebContact.LastName
FROM dbo.tblApp_WebContact
ORDER BY tblApp_WebContact.LastName, tblApp_WebContact.FirstName
As Erland already mentioned.
ML|||"ML" <ML@.discussions.microsoft.com> wrote in message
news:B8F58855-C6D2-4D7E-B02A-F606A3B1A2E3@.microsoft.com...
> There's just one thing missing: TOP must be specified in the SELECT
> statement
> of a view if ORDER BY is specified.
> SELECT TOP 100 PERCENT
Well I'm not sure about the OP, but it's answered my question.
Thanks|||So, I guess my mother was right - sometimes I answer questions before they
were asked. :)
ML|||In this view definition, the ORDER BY clause has no function, because it
does not change the resultset (since you want to select 100 PERCENT).
And since a view has no implicit order, an ORDER BY clause for sorting
purposes does not work.
If you want a specific order, then you must specify an ORDER BY clause
when you select from the view.
Gert-Jan
Man Utd wrote:
> I cannot make the result set ORDER BY LastName and FirstName:
> CREATE VIEW dbo.viewApp_Web_ContactDetails
> AS
> SELECT TOP 100 PERCENT dbo.tblApp_Contact.Salutation,
> dbo.tblApp_Contact.FirstName,
> dbo.tblApp_Contact.LastName
> FROM dbo.tblApp_Contact
> ORDER BY tblApp_Contact.LastName, tblApp_Contact.FirstName
> UNION ALL
> SELECT TOP 100 PERCENT dbo.tblApp_WebContact.Salutation,
> dbo.tblApp_WebContact.FirstName,
> dbo.tblApp_WebContact.LastName
> FROM dbo.tblApp_WebContact
> ORDER BY tblApp_WebContact.LastName, tblApp_WebContact.FirstName
> How do I make the result set in the order by LastName and then FirstName ?
Friday, March 23, 2012
order by cluase cause wrong results to be returned.
I have the follow table.
/****** Object: Table [dbo].[deletethisTempOut] Script Date: 09/10/2007 09:20:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[deletethisTempOut](
[ThemeName] [varchar](60) NULL,
[intLocationCount] [int] NULL,
[dblRepValueA] [float] NULL,
[dblRepValueB] [float] NULL,
[dblRepValueC] [float] NULL,
[dblRepValueD] [float] NULL,
[dblTotalRepValue] [float] NULL,
[dblLimit1] [float] NULL,
[dblLimit2] [float] NULL,
[dblLimit3] [float] NULL,
[dblLimit4] [float] NULL,
[dblTotalLimit] [float] NULL,
[fltEmployeecount] [float] NULL,
[intAreaLevel1] [tinyint] NOT NULL,
[strFullName] [varchar](13) NOT NULL,
[strAreaLevel2] [varchar](20) NOT NULL,
[strAreaLevel3] [varchar](20) NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
If I use the following SQL:
SELECT ThemeName, intLocationCount, dblRepValueA, dblRepValueB, dblRepValueC, dblRepValueD, dblTotalRepValue, dblLimit1, dblLimit2, dblLimit3,
dblLimit4, dblTotalLimit, fltEmployeecount, intAreaLevel1, strFullName, strAreaLevel2, strAreaLevel3
FROM deletethisTempOut
ORDER BY strAreaLevel2, strAreaLevel3
GET Following correct results:
IF I use the following SQL I get the wrong results:
SELECT ThemeName, intLocationCount, dblRepValueA, dblRepValueB, dblRepValueC, dblRepValueD, dblTotalRepValue, dblLimit1, dblLimit2, dblLimit3,
dblLimit4, dblTotalLimit, fltEmployeecount, intAreaLevel1, strFullName, strAreaLevel2, strAreaLevel3
FROM deletethisTempOut
ORDER BY ThemeName
WRONG results:
In what way do you think the results are wrong? Do you mean that the Themename values are not ordered correctly?
If so, then remember that when you have a column which has duplicate entries, there is nothing to tell sql server to output them in any particular order. You are just ordering your records based upon the value of that column ony. If you want duplicate entries to be ordered by intLocationCount, you'll need to specify that in your order clause too.
Eg ORDER BY ThemeName, intLocationCount DESC
HTH!
|||when the themename is used I get multiple rows when the temp table contains only one row for each themename.
themename is based on the level2 and level3 values and there is only one row for every level2 and level3 combination.
in this case level2 = state code and level3 = county code. Themename should be county name.
If you can tell me how to send you the under lying table I will.
|||This is not possible, the order by clause does not affect the number of rows returned merely their ordering.
More records must have been inserted into the underlying table after the initial query was run and before the subsequent query was run.
|||This a a very repeatable problem can I send you the underlying table? I dump the temp table to a perm table to check the results and I got the same behovior eventhough when you a select * from the table you only get one row from the table for each themename.
I agree this should never happen especial since its a single table no join.....
|||Since you said there is only one row, I reverse-engineered it and created the following:
Code Snippet
USE tempdb;
GO
/****** Object: Table [dbo].[deletethisTempOut] Script Date: 09/10/2007 09:20:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[deletethisTempOut](
[ThemeName] [varchar](60) NULL,
[intLocationCount] [int] NULL,
[dblRepValueA] [float] NULL,
[dblRepValueB] [float] NULL,
[dblRepValueC] [float] NULL,
[dblRepValueD] [float] NULL,
[dblTotalRepValue] [float] NULL,
[dblLimit1] [float] NULL,
[dblLimit2] [float] NULL,
[dblLimit3] [float] NULL,
[dblLimit4] [float] NULL,
[dblTotalLimit] [float] NULL,
[fltEmployeecount] [float] NULL,
[intAreaLevel1] [tinyint] NOT NULL,
[strFullName] [varchar](13) NOT NULL,
[strAreaLevel2] [varchar](20) NOT NULL,
[strAreaLevel3] [varchar](20) NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
Go
INSERT deletethisTempOut (ThemeName, intLocationCount, dblRepValueA, dblRepValueB, dblRepValueC, dblRepValueD, dblTotalRepValue, dblLimit1, dblLimit2, dblLimit3,
dblLimit4, dblTotalLimit, fltEmployeecount, intAreaLevel1, strFullName, strAreaLevel2, strAreaLevel3
) VALUES ('Adair',284,899989594,0,574857716,190479902,1665327212,0,0,0,0,1665327212,0,1,'United,States',1,1)
GO
-- Query 1
SELECT ThemeName, intLocationCount, dblRepValueA, dblRepValueB, dblRepValueC, dblRepValueD, dblTotalRepValue, dblLimit1, dblLimit2, dblLimit3,
dblLimit4, dblTotalLimit, fltEmployeecount, intAreaLevel1, strFullName, strAreaLevel2, strAreaLevel3
FROM deletethisTempOut
ORDER BY strAreaLevel2, strAreaLevel3
GO
-- Query 2
SELECT ThemeName, intLocationCount, dblRepValueA, dblRepValueB, dblRepValueC, dblRepValueD, dblTotalRepValue, dblLimit1, dblLimit2, dblLimit3,
dblLimit4, dblTotalLimit, fltEmployeecount, intAreaLevel1, strFullName, strAreaLevel2, strAreaLevel3
FROM deletethisTempOut
ORDER BY ThemeName
GO
I couldn't repro the problem you are having.
Is this similar to what you have? Are you missing out a WHERE clause in one of the queries?
|||Caveman,
You'll have to provide more information. You have mentioned a temp table, but the code you posted does not refer to a temp table. Later you said something about a permanent table and a temp table, again without being specific about anything.
If I had to guess (which I do in this case), my suspicion is that perhaps you are seeing problems SELECTing from a view where the view definition contains things like UNION, TOP, or ORDER BY. It is possible, however, that you are encountering a bug.
Can you run SELECT @.@.VERSION and either post the result or find out whether you have installed the latest service pack for the version of SQL Server you're running?
It probably won't help to send anyone the data in the underlying table. What we need to see to help is the *exact* queries that you have problems with, and all underlying definitions. For example, if the problem query involves a temporary table, we need to see the code that inserts data into the temporary table. If there is a view somewhere, we need to see its definition.
Steve Kass
Drew University
http://www.stevekass.com
order by cluase cause wrong results to be returned.
I have the follow table.
/****** Object: Table [dbo].[deletethisTempOut] Script Date: 09/10/2007 09:20:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[deletethisTempOut](
[ThemeName] [varchar](60) NULL,
[intLocationCount] [int] NULL,
[dblRepValueA] [float] NULL,
[dblRepValueB] [float] NULL,
[dblRepValueC] [float] NULL,
[dblRepValueD] [float] NULL,
[dblTotalRepValue] [float] NULL,
[dblLimit1] [float] NULL,
[dblLimit2] [float] NULL,
[dblLimit3] [float] NULL,
[dblLimit4] [float] NULL,
[dblTotalLimit] [float] NULL,
[fltEmployeecount] [float] NULL,
[intAreaLevel1] [tinyint] NOT NULL,
[strFullName] [varchar](13) NOT NULL,
[strAreaLevel2] [varchar](20) NOT NULL,
[strAreaLevel3] [varchar](20) NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
If I use the following SQL:
SELECT ThemeName, intLocationCount, dblRepValueA, dblRepValueB, dblRepValueC, dblRepValueD, dblTotalRepValue, dblLimit1, dblLimit2, dblLimit3,
dblLimit4, dblTotalLimit, fltEmployeecount, intAreaLevel1, strFullName, strAreaLevel2, strAreaLevel3
FROM deletethisTempOut
ORDER BY strAreaLevel2, strAreaLevel3
GET Following correct results:
IF I use the following SQL I get the wrong results:
SELECT ThemeName, intLocationCount, dblRepValueA, dblRepValueB, dblRepValueC, dblRepValueD, dblTotalRepValue, dblLimit1, dblLimit2, dblLimit3,
dblLimit4, dblTotalLimit, fltEmployeecount, intAreaLevel1, strFullName, strAreaLevel2, strAreaLevel3
FROM deletethisTempOut
ORDER BY ThemeName
WRONG results:
In what way do you think the results are wrong? Do you mean that the Themename values are not ordered correctly?
If so, then remember that when you have a column which has duplicate entries, there is nothing to tell sql server to output them in any particular order. You are just ordering your records based upon the value of that column ony. If you want duplicate entries to be ordered by intLocationCount, you'll need to specify that in your order clause too.
Eg ORDER BY ThemeName, intLocationCount DESC
HTH!
|||when the themename is used I get multiple rows when the temp table contains only one row for each themename.
themename is based on the level2 and level3 values and there is only one row for every level2 and level3 combination.
in this case level2 = state code and level3 = county code. Themename should be county name.
If you can tell me how to send you the under lying table I will.
|||This is not possible, the order by clause does not affect the number of rows returned merely their ordering.
More records must have been inserted into the underlying table after the initial query was run and before the subsequent query was run.
|||This a a very repeatable problem can I send you the underlying table? I dump the temp table to a perm table to check the results and I got the same behovior eventhough when you a select * from the table you only get one row from the table for each themename.
I agree this should never happen especial since its a single table no join.....
|||Since you said there is only one row, I reverse-engineered it and created the following:
Code Snippet
USE tempdb;
GO
/****** Object: Table [dbo].[deletethisTempOut] Script Date: 09/10/2007 09:20:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[deletethisTempOut](
[ThemeName] [varchar](60) NULL,
[intLocationCount] [int] NULL,
[dblRepValueA] [float] NULL,
[dblRepValueB] [float] NULL,
[dblRepValueC] [float] NULL,
[dblRepValueD] [float] NULL,
[dblTotalRepValue] [float] NULL,
[dblLimit1] [float] NULL,
[dblLimit2] [float] NULL,
[dblLimit3] [float] NULL,
[dblLimit4] [float] NULL,
[dblTotalLimit] [float] NULL,
[fltEmployeecount] [float] NULL,
[intAreaLevel1] [tinyint] NOT NULL,
[strFullName] [varchar](13) NOT NULL,
[strAreaLevel2] [varchar](20) NOT NULL,
[strAreaLevel3] [varchar](20) NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
Go
INSERT deletethisTempOut (ThemeName, intLocationCount, dblRepValueA, dblRepValueB, dblRepValueC, dblRepValueD, dblTotalRepValue, dblLimit1, dblLimit2, dblLimit3,
dblLimit4, dblTotalLimit, fltEmployeecount, intAreaLevel1, strFullName, strAreaLevel2, strAreaLevel3
) VALUES ('Adair',284,899989594,0,574857716,190479902,1665327212,0,0,0,0,1665327212,0,1,'United,States',1,1)
GO
-- Query 1
SELECT ThemeName, intLocationCount, dblRepValueA, dblRepValueB, dblRepValueC, dblRepValueD, dblTotalRepValue, dblLimit1, dblLimit2, dblLimit3,
dblLimit4, dblTotalLimit, fltEmployeecount, intAreaLevel1, strFullName, strAreaLevel2, strAreaLevel3
FROM deletethisTempOut
ORDER BY strAreaLevel2, strAreaLevel3
GO
-- Query 2
SELECT ThemeName, intLocationCount, dblRepValueA, dblRepValueB, dblRepValueC, dblRepValueD, dblTotalRepValue, dblLimit1, dblLimit2, dblLimit3,
dblLimit4, dblTotalLimit, fltEmployeecount, intAreaLevel1, strFullName, strAreaLevel2, strAreaLevel3
FROM deletethisTempOut
ORDER BY ThemeName
GO
I couldn't repro the problem you are having.
Is this similar to what you have? Are you missing out a WHERE clause in one of the queries?
|||Caveman,
You'll have to provide more information. You have mentioned a temp table, but the code you posted does not refer to a temp table. Later you said something about a permanent table and a temp table, again without being specific about anything.
If I had to guess (which I do in this case), my suspicion is that perhaps you are seeing problems SELECTing from a view where the view definition contains things like UNION, TOP, or ORDER BY. It is possible, however, that you are encountering a bug.
Can you run SELECT @.@.VERSION and either post the result or find out whether you have installed the latest service pack for the version of SQL Server you're running?
It probably won't help to send anyone the data in the underlying table. What we need to see to help is the *exact* queries that you have problems with, and all underlying definitions. For example, if the problem query involves a temporary table, we need to see the code that inserts data into the temporary table. If there is a view somewhere, we need to see its definition.
Steve Kass
Drew University
http://www.stevekass.com
sqlorder by cluase cause wrong results to be returned.
I have the follow table.
/****** Object: Table [dbo].[deletethisTempOut] Script Date: 09/10/2007 09:20:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[deletethisTempOut](
[ThemeName] [varchar](60) NULL,
[intLocationCount] [int] NULL,
[dblRepValueA] [float] NULL,
[dblRepValueB] [float] NULL,
[dblRepValueC] [float] NULL,
[dblRepValueD] [float] NULL,
[dblTotalRepValue] [float] NULL,
[dblLimit1] [float] NULL,
[dblLimit2] [float] NULL,
[dblLimit3] [float] NULL,
[dblLimit4] [float] NULL,
[dblTotalLimit] [float] NULL,
[fltEmployeecount] [float] NULL,
[intAreaLevel1] [tinyint] NOT NULL,
[strFullName] [varchar](13) NOT NULL,
[strAreaLevel2] [varchar](20) NOT NULL,
[strAreaLevel3] [varchar](20) NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
If I use the following SQL:
SELECT ThemeName, intLocationCount, dblRepValueA, dblRepValueB, dblRepValueC, dblRepValueD, dblTotalRepValue, dblLimit1, dblLimit2, dblLimit3,
dblLimit4, dblTotalLimit, fltEmployeecount, intAreaLevel1, strFullName, strAreaLevel2, strAreaLevel3
FROM deletethisTempOut
ORDER BY strAreaLevel2, strAreaLevel3
GET Following correct results:
IF I use the following SQL I get the wrong results:
SELECT ThemeName, intLocationCount, dblRepValueA, dblRepValueB, dblRepValueC, dblRepValueD, dblTotalRepValue, dblLimit1, dblLimit2, dblLimit3,
dblLimit4, dblTotalLimit, fltEmployeecount, intAreaLevel1, strFullName, strAreaLevel2, strAreaLevel3
FROM deletethisTempOut
ORDER BY ThemeName
WRONG results:
In what way do you think the results are wrong? Do you mean that the Themename values are not ordered correctly?
If so, then remember that when you have a column which has duplicate entries, there is nothing to tell sql server to output them in any particular order. You are just ordering your records based upon the value of that column ony. If you want duplicate entries to be ordered by intLocationCount, you'll need to specify that in your order clause too.
Eg ORDER BY ThemeName, intLocationCount DESC
HTH!
|||when the themename is used I get multiple rows when the temp table contains only one row for each themename.
themename is based on the level2 and level3 values and there is only one row for every level2 and level3 combination.
in this case level2 = state code and level3 = county code. Themename should be county name.
If you can tell me how to send you the under lying table I will.
|||This is not possible, the order by clause does not affect the number of rows returned merely their ordering.
More records must have been inserted into the underlying table after the initial query was run and before the subsequent query was run.
|||This a a very repeatable problem can I send you the underlying table? I dump the temp table to a perm table to check the results and I got the same behovior eventhough when you a select * from the table you only get one row from the table for each themename.
I agree this should never happen especial since its a single table no join.....
|||Since you said there is only one row, I reverse-engineered it and created the following:
Code Snippet
USE tempdb;
GO
/****** Object: Table [dbo].[deletethisTempOut] Script Date: 09/10/2007 09:20:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[deletethisTempOut](
[ThemeName] [varchar](60) NULL,
[intLocationCount] [int] NULL,
[dblRepValueA] [float] NULL,
[dblRepValueB] [float] NULL,
[dblRepValueC] [float] NULL,
[dblRepValueD] [float] NULL,
[dblTotalRepValue] [float] NULL,
[dblLimit1] [float] NULL,
[dblLimit2] [float] NULL,
[dblLimit3] [float] NULL,
[dblLimit4] [float] NULL,
[dblTotalLimit] [float] NULL,
[fltEmployeecount] [float] NULL,
[intAreaLevel1] [tinyint] NOT NULL,
[strFullName] [varchar](13) NOT NULL,
[strAreaLevel2] [varchar](20) NOT NULL,
[strAreaLevel3] [varchar](20) NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
Go
INSERT deletethisTempOut (ThemeName, intLocationCount, dblRepValueA, dblRepValueB, dblRepValueC, dblRepValueD, dblTotalRepValue, dblLimit1, dblLimit2, dblLimit3,
dblLimit4, dblTotalLimit, fltEmployeecount, intAreaLevel1, strFullName, strAreaLevel2, strAreaLevel3
) VALUES ('Adair',284,899989594,0,574857716,190479902,1665327212,0,0,0,0,1665327212,0,1,'United,States',1,1)
GO
-- Query 1
SELECT ThemeName, intLocationCount, dblRepValueA, dblRepValueB, dblRepValueC, dblRepValueD, dblTotalRepValue, dblLimit1, dblLimit2, dblLimit3,
dblLimit4, dblTotalLimit, fltEmployeecount, intAreaLevel1, strFullName, strAreaLevel2, strAreaLevel3
FROM deletethisTempOut
ORDER BY strAreaLevel2, strAreaLevel3
GO
-- Query 2
SELECT ThemeName, intLocationCount, dblRepValueA, dblRepValueB, dblRepValueC, dblRepValueD, dblTotalRepValue, dblLimit1, dblLimit2, dblLimit3,
dblLimit4, dblTotalLimit, fltEmployeecount, intAreaLevel1, strFullName, strAreaLevel2, strAreaLevel3
FROM deletethisTempOut
ORDER BY ThemeName
GO
I couldn't repro the problem you are having.
Is this similar to what you have? Are you missing out a WHERE clause in one of the queries?
|||Caveman,
You'll have to provide more information. You have mentioned a temp table, but the code you posted does not refer to a temp table. Later you said something about a permanent table and a temp table, again without being specific about anything.
If I had to guess (which I do in this case), my suspicion is that perhaps you are seeing problems SELECTing from a view where the view definition contains things like UNION, TOP, or ORDER BY. It is possible, however, that you are encountering a bug.
Can you run SELECT @.@.VERSION and either post the result or find out whether you have installed the latest service pack for the version of SQL Server you're running?
It probably won't help to send anyone the data in the underlying table. What we need to see to help is the *exact* queries that you have problems with, and all underlying definitions. For example, if the problem query involves a temporary table, we need to see the code that inserts data into the temporary table. If there is a view somewhere, we need to see its definition.
Steve Kass
Drew University
http://www.stevekass.com
Order by clause in View doesn''t order.
I have created view by jaoining two table and have order by clause.
The sql generated is as follows
SELECT TOP (100) PERCENT dbo.UWYearDetail.*, dbo.UWYearGroup.*
FROM dbo.UWYearDetail INNER JOIN
dbo.UWYearGroup ON dbo.UWYearDetail.UWYearGroupId = dbo.UWYearGroup.UWYearGroupId
ORDER BY dbo.UWYearDetail.PlanVersionId, dbo.UWYearGroup.UWFinancialPlanSegmentId, dbo.UWYearGroup.UWYear, dbo.UWYearGroup.MandDFlag,
dbo.UWYearGroup.EarningsMethod, dbo.UWYearGroup.EffectiveMonth
If I run sql the results are displayed in proper order but the view only order by first item in order by clause.
Has somebody experience same thing? How to fix this issue?
Thanks,
From Books Online 2005: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/bb394abe-cae6-4905-b5c6-8daaded77742.htm When ORDER BY is used in the definition of a view, inline function, derived table, or subquery, the clause is used only to determine the rows returned by the TOP clause. The ORDER BY clause does not guarantee ordered results when these constructs are queried, unless ORDER BY is also specified in the query itself. If you want to see the contents of the view in a particular order, you must specify ORDER BY when you query the view. The ORDER BY in the view definition here does nothing at all. It's unfortunate that TOP (100) PERCENT is allowed, let alone added by the view designer, and my suggestion is never use TOP (100) PERCENT, because it is meaningless and leads you to think you can create an "ordered view", which you cannot. Steve Kass Drew University sg2000@.discussions.microsoft.com wrote:
> I have created view by jaoining two table and have order by clause.
>
> The sql generated is as follows
>
> SELECT TOP (100) PERCENT dbo.UWYearDetail.*, dbo.UWYearGroup.*
> FROM dbo.UWYearDetail INNER JOIN
> dbo.UWYearGroup ON dbo.UWYearDetail.UWYearGroupId
> = dbo.UWYearGroup.UWYearGroupId
> ORDER BY dbo.UWYearDetail.PlanVersionId,
> dbo.UWYearGroup.UWFinancialPlanSegmentId, dbo.UWYearGroup.UWYear,
> dbo.UWYearGroup.MandDFlag,
> dbo.UWYearGroup.EarningsMethod,
> dbo.UWYearGroup.EffectiveMonth
>
>
>
> If I run sql the results are displayed in proper order but the view only
> order by first item in order by clause.
>
> Has somebody experience same thing? How to fix this issue?
>
> Thanks,
>
>
>
>
This is a common complaint, but if order by wasn't allowed, then TOP (anything other than 100%) would not be of much use. That is why you can't put an order by clause in the view definition without the TOP clause.
The bottom line is, like Steve said, there is no guarantee that tables have any order, including views with Order By clauses, Tables with clustered indexes, etc. This allows the optimizer to work with the hardware in the very fastest manner if you don't explicitly ask for rows in a given order.
It's kind of annoying, but it makes sense and is not a real problem once you get used to it (and it took me a long time when I first realized it too :)
|||We have lot of views in SQL server 2000. It will be big pain to convert to SQL 2005. i.e. we need to change views to remove 'order by' and then need to identify the calls to the view and add order by instead. This is going to be big problem in conversion from SQL Server 200 to SQL Server 2005 going forward. Do you see any easy way to acomplish this?
|||Hello,
I dont know if its allowed here, but there is a tool that allows you to find references to SQL Server objects even in sourcecode. Take a closer loot at the apexsql suite of tools. They will help you to identify where calls to those views are made. I am not sure which product was able to do this, but i think it was ApexSQL Clean ( http://www.apexsql.com/sql_tools_clean.asp )
If tossing this add in here was not allowed post so and ill remove the link.
(Edit: Free No-Fuss trial Version is available for 30 days)
|||It is allowed as long as it is on topic and not just an advertisement. Giving us information about legit tools that help with SQL Server development/managment is great.
|||Yes the tools will be helpful but what if my application is calling view from dlls or externally. I don't think any tool will hepl here. I have application built with rapid application builder called Ironspeed and it generates code which is using view.
What are my options other than manully going in my application to fix ( which seems painful), to keep the things working as it were before in SQL 2000?
|||There are no options unfortunately. Specifying ORDER BY in the outermost SELECT statement in your query is the only way to ensure that the rows are returned in a particular order to the client.|||Hi sg2000,
You can use
TOP (99) PERCENT
instead of
TOP (100) PERCENT
The results for the query "Select top 99 percent..." from a table, which has X row(s), is X row(s), even if the table has only one row.
Then, you will be able to use the "order by" clause whitout need to change anything in your application and without giving less rows as result.
|||This still doesn't guarantee that rows will be returned in the same order to client. The whole point is not about use of TOP 100 PERCENT with ORDER BY. Any ORDER BY clause in derived table or view for example only applies within that scope. To present rows in a particular order to client, you HAVE to include ORDER BY clause in the outermost SELECT statement. This is the only way the query will produce expected results always. See blog post below also for ordering guarantees in SQL Server:
http://blogs.msdn.com/sqltips/archive/2005/07/20/441053.aspx
|||OK, this is absolutely retarded.
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/bb394abe-cae6-4905-b5c6-8daaded77742.htm
This says :
Specifies the sort order used on columns returned in a SELECT statement. The ORDER BY clause is not valid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.
If it says "The ORDER BY clause is not valid in views" then why the heck is there a "sort by" column in the New View Screen?
|||
Because of the last bit:
The ORDER BY clause is not valid in views, inline functions, derived tables, and subqueries, unless TOP is also specified
And yes, it is still a bit retarded, but the whole New View screen is pretty unpleasant if you ask me. And yes again, it is ironic that a tool that is made to make things easier for newer users often makes it less easy. Go figure :)
|||not trying to be harsh...i had a little wine in me at the time and things just came out. (this is my drunk apology haha) ...|||No problemo. I agree with you wholeheartedly that it should be better than it is, either way :)sqlOrder by clause in View doesn't order.
I have created view by jaoining two table and have order by clause.
The sql generated is as follows
SELECT TOP (100) PERCENT dbo.UWYearDetail.*, dbo.UWYearGroup.*
FROM dbo.UWYearDetail INNER JOIN
dbo.UWYearGroup ON dbo.UWYearDetail.UWYearGroupId = dbo.UWYearGroup.UWYearGroupId
ORDER BY dbo.UWYearDetail.PlanVersionId, dbo.UWYearGroup.UWFinancialPlanSegmentId, dbo.UWYearGroup.UWYear, dbo.UWYearGroup.MandDFlag,
dbo.UWYearGroup.EarningsMethod, dbo.UWYearGroup.EffectiveMonth
If I run sql the results are displayed in proper order but the view only order by first item in order by clause.
Has somebody experience same thing? How to fix this issue?
Thanks,
From Books Online 2005: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/bb394abe-cae6-4905-b5c6-8daaded77742.htm When ORDER BY is used in the definition of a view, inline function, derived table, or subquery, the clause is used only to determine the rows returned by the TOP clause. The ORDER BY clause does not guarantee ordered results when these constructs are queried, unless ORDER BY is also specified in the query itself. If you want to see the contents of the view in a particular order, you must specify ORDER BY when you query the view. The ORDER BY in the view definition here does nothing at all. It's unfortunate that TOP (100) PERCENT is allowed, let alone added by the view designer, and my suggestion is never use TOP (100) PERCENT, because it is meaningless and leads you to think you can create an "ordered view", which you cannot. Steve Kass Drew University sg2000@.discussions.microsoft.com wrote:
> I have created view by jaoining two table and have order by clause.
>
> The sql generated is as follows
>
> SELECT TOP (100) PERCENT dbo.UWYearDetail.*, dbo.UWYearGroup.*
> FROM dbo.UWYearDetail INNER JOIN
> dbo.UWYearGroup ON dbo.UWYearDetail.UWYearGroupId
> = dbo.UWYearGroup.UWYearGroupId
> ORDER BY dbo.UWYearDetail.PlanVersionId,
> dbo.UWYearGroup.UWFinancialPlanSegmentId, dbo.UWYearGroup.UWYear,
> dbo.UWYearGroup.MandDFlag,
> dbo.UWYearGroup.EarningsMethod,
> dbo.UWYearGroup.EffectiveMonth
>
>
>
> If I run sql the results are displayed in proper order but the view only
> order by first item in order by clause.
>
> Has somebody experience same thing? How to fix this issue?
>
> Thanks,
>
>
>
>
This is a common complaint, but if order by wasn't allowed, then TOP (anything other than 100%) would not be of much use. That is why you can't put an order by clause in the view definition without the TOP clause.
The bottom line is, like Steve said, there is no guarantee that tables have any order, including views with Order By clauses, Tables with clustered indexes, etc. This allows the optimizer to work with the hardware in the very fastest manner if you don't explicitly ask for rows in a given order.
It's kind of annoying, but it makes sense and is not a real problem once you get used to it (and it took me a long time when I first realized it too :)
|||We have lot of views in SQL server 2000. It will be big pain to convert to SQL 2005. i.e. we need to change views to remove 'order by' and then need to identify the calls to the view and add order by instead. This is going to be big problem in conversion from SQL Server 200 to SQL Server 2005 going forward. Do you see any easy way to acomplish this?
|||Hello,
I dont know if its allowed here, but there is a tool that allows you to find references to SQL Server objects even in sourcecode. Take a closer loot at the apexsql suite of tools. They will help you to identify where calls to those views are made. I am not sure which product was able to do this, but i think it was ApexSQL Clean ( http://www.apexsql.com/sql_tools_clean.asp )
If tossing this add in here was not allowed post so and ill remove the link.
(Edit: Free No-Fuss trial Version is available for 30 days)
|||It is allowed as long as it is on topic and not just an advertisement. Giving us information about legit tools that help with SQL Server development/managment is great.
|||Yes the tools will be helpful but what if my application is calling view from dlls or externally. I don't think any tool will hepl here. I have application built with rapid application builder called Ironspeed and it generates code which is using view.
What are my options other than manully going in my application to fix ( which seems painful), to keep the things working as it were before in SQL 2000?
|||There are no options unfortunately. Specifying ORDER BY in the outermost SELECT statement in your query is the only way to ensure that the rows are returned in a particular order to the client.|||Hi sg2000,
You can use
TOP (99) PERCENT
instead of
TOP (100) PERCENT
The results for the query "Select top 99 percent..." from a table, which has X row(s), is X row(s), even if the table has only one row.
Then, you will be able to use the "order by" clause whitout need to change anything in your application and without giving less rows as result.
|||This still doesn't guarantee that rows will be returned in the same order to client. The whole point is not about use of TOP 100 PERCENT with ORDER BY. Any ORDER BY clause in derived table or view for example only applies within that scope. To present rows in a particular order to client, you HAVE to include ORDER BY clause in the outermost SELECT statement. This is the only way the query will produce expected results always. See blog post below also for ordering guarantees in SQL Server:
http://blogs.msdn.com/sqltips/archive/2005/07/20/441053.aspx
|||OK, this is absolutely retarded.
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/bb394abe-cae6-4905-b5c6-8daaded77742.htm
This says :
Specifies the sort order used on columns returned in a SELECT statement. The ORDER BY clause is not valid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.
If it says "The ORDER BY clause is not valid in views" then why the heck is there a "sort by" column in the New View Screen?
|||
Because of the last bit:
The ORDER BY clause is not valid in views, inline functions, derived tables, and subqueries, unless TOP is also specified
And yes, it is still a bit retarded, but the whole New View screen is pretty unpleasant if you ask me. And yes again, it is ironic that a tool that is made to make things easier for newer users often makes it less easy. Go figure :)
|||not trying to be harsh...i had a little wine in me at the time and things just came out. (this is my drunk apology haha) ...|||No problemo. I agree with you wholeheartedly that it should be better than it is, either way :)|||I had this problem and solve change TOP 100 PERCENT by TOP 999999999999.
Subscribe to:
Posts (Atom)