asked on

Deferent recordset when running a query 1,2,3 or more times eachtime its different

SQL Server 2012:

I have a query that pulls data from a static table that has no transactions pushing into it, Every time I run the statement I get a different record set back, can anyone explain WHY to me, and How to fix it?

THE SCRIPT:

SELECT *
INTO WTM_tmpHC
FROM ( SELECT * ,ROW_NUMBER() OVER ( PARTITION BY Sm, CSUMId, PCCN ORDER BY DOSFrom ) AS recordnumber
FROM WTM_tMC
) a
WHERE recordnumber = 1;

Here is the create statement for the table WTM_tMC

CREATE TABLE [dbo].[WTM2_tMC](
      [SM] [VARCHAR](MAX) NULL,
      [CSUMId] [VARCHAR](MAX) NULL,
[PCCN] [VARCHAR](MAX) NULL,
      [PID] [VARCHAR](MAX) NULL,
      [PCCN] [VARCHAR](MAX) NULL,
      [DOSFrom] [VARCHAR](MAX) NULL,
      [DOSThru] [VARCHAR](MAX) NULL,
      [PA] [DECIMAL](38, 2) NULL,
      [CA] [DECIMAL](38, 2) NULL,
      [CIA] [DECIMAL](38, 2) NULL,
      [DA] [DECIMAL](38, 2) NULL,
      [PF] [INT] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Scott Pletcher

Hmm, interesting, SQL must be truncating the DOSFrom column itself, since I don't think you can sort on a MAX value.

At any rate, the combination of columns:
Sm, CSUMId, PCCN
must not be unique, and must have the same DOSFrom value(s). Since you're limiting to one row per unique combination of those values, if those values appear in multiple rows, any one of them could be chosen each time you run the query.

Pawan Kumar

Hi Tyler,

I think the column DOSFrom (of type Varchar(MAX)) is the problem. Dont you have any INT/BIGINT fields to do order by. Normally we do order by INT/Date columns.
Please try below- <Execute below few times and see if you are getting things in line or not>

--
SELECT * FROM    
( 
	SELECT * ,ROW_NUMBER() OVER ( PARTITION BY Sm, CSUMId, PCCN ORDER BY Sm, CSUMId, PCCN, DOSFrom ) AS recordnumber FROM WTM_tMC
) a
WHERE recordnumber = 1
--

Open in new window

Hope it helps!

ASKER CERTIFIED SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Tyler

ASKER

This was what I was looking for.