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
sql
No comments:
Post a Comment