Order By Adding Rows?

Hi All,
Having trouble with the following SQL statement. If there is multiple
ORDER by's it adds rows to the result. The SQL statement below adds
rows to the result, if I remove block_line_no
from the orer by it returns the correct data.
SELECT *
FROM nc_block_data
WHERE (PROGRAM_ID = 2607) OR
(PROGRAM_ID = 2608) OR
(PROGRAM_ID = 2609) OR
(PROGRAM_ID = 2610) OR
(PROGRAM_ID = 2612) OR
(PROGRAM_ID = 2613) OR
(PROGRAM_ID = 2614) OR
(PROGRAM_ID = 2615) OR
(PROGRAM_ID = 2616) OR
(PROGRAM_ID = 2617) OR
(PROGRAM_ID = 2618) OR
(PROGRAM_ID = 2619) OR
(PROGRAM_ID = 2620) OR
(PROGRAM_ID = 2621)
ORDER BY program_id, block_line_noSkip wrote:
> Hi All,
> Having trouble with the following SQL statement. If there is multiple
> ORDER by's it adds rows to the result. The SQL statement below adds
> rows to the result, if I remove block_line_no
> from the orer by it returns the correct data.
>
> SELECT *
> FROM nc_block_data
> WHERE (PROGRAM_ID = 2607) OR
> (PROGRAM_ID = 2608) OR
> (PROGRAM_ID = 2609) OR
> (PROGRAM_ID = 2610) OR
> (PROGRAM_ID = 2612) OR
> (PROGRAM_ID = 2613) OR
> (PROGRAM_ID = 2614) OR
> (PROGRAM_ID = 2615) OR
> (PROGRAM_ID = 2616) OR
> (PROGRAM_ID = 2617) OR
> (PROGRAM_ID = 2618) OR
> (PROGRAM_ID = 2619) OR
> (PROGRAM_ID = 2620) OR
> (PROGRAM_ID = 2621)
> ORDER BY program_id, block_line_no
Can you post some code to reproduce that problem - CREATE TABLE
followed by INSERTs of one or two sample rows. Also tell us what
version, edition and service pack of SQL Server you are using. If your
server is patched and you can't reproduce the problem then maybe you
are looking at some corrupt data or index somewhere.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Here is the table structure. I created a test table but was not able to
reproduce the problem. Could it be something with the table structure?
USE [iFrame]
GO
/****** Object: Table [dbo].[Nc_Block_Data] Script Date: 04/11/2006
14:32:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Nc_Block_Data](
[PROGRAM_ID] [int] NOT NULL,
[BLOCK_LINE_NO] [int] NOT NULL,
[PROG_LINE_NO] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL,
[NC_CODE] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[HOLE_NAME] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[NCBLOCK_STATUS] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL,
[NCBLOCK_STATUS_DT] [datetime] NULL,
[ERROR_INF] [varchar](80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF|||Not sure why but it looks like it was bad data, just in one section of
the DB. Does this make sense? If I grab a different group of data all
works fine.|||If you can be certain that the data is not corrupt, try reindexing the
suspicious block_line_no column.
ML
http://milambda.blogspot.com/sql