• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 385
  • Last Modified:

Sql query returning different results each time it is run even though conditions are the same.

I have the following query which works.

SELECT     count(*)
FROM         (SELECT     EntSys,School_code,school_name,ADMISSION_DATE,HP_Type,HP_CODE,HP_Name, ROW_NUMBER() OVER (PARTITION BY EntSys
                       ORDER BY ADMISSION_DATE desc) rn
FROM         [All Schools Excluding Nurseries]) sq
WHERE     sq.RN = 1

However when I add more WHERE conditions it become unstable and does not return a consistent result.

For example:


SELECT     count(*)
FROM         (SELECT     EntSys,School_code,school_name,ADMISSION_DATE,HP_Type,HP_CODE,HP_Name, ROW_NUMBER() OVER (PARTITION BY EntSys
                       ORDER BY ADMISSION_DATE desc) rn
FROM         [All Schools Excluding Nurseries]) sq
WHERE     sq.RN = 1 and  HP_Type='SN'

This will return a count of 64429 the first time it is run.

The second time it runs the count = 64433
The forth time it runs the count = 64644  
and so on... it is not stable.

Can anybody think of a reason why the extra WHERE condition causes such a problem.
0
Mr_Shaw
Asked:
Mr_Shaw
  • 7
  • 5
  • 2
2 Solutions
 
Reza RadConsultant, TrainerCommented:
is your table records growing?
It seems that your count is bigger each time,
I think some records insert into table from where
0
 
tigin44Commented:
The queries seems correct and should produce the correct result set. Should some users adding new data to the table? If so the query will always yield different results.  And why are you using such an inner query for taking a simple count?
0
 
Mr_ShawAuthor Commented:
Maybe. But why is this happening.

If I don't have a count and just return a row another strage thing happens...sometimes it returns data and sometime it does not return data.... strange.
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
tigin44Commented:
do the query execution takes long? your query may be time out?
0
 
Mr_ShawAuthor Commented:
I am using a count for testing purposes.

I am trying to solve  the other problem of ...sometimes it returns data and sometime it does not return data....
0
 
tigin44Commented:
what is the approximate query execution time?
0
 
Reza RadConsultant, TrainerCommented:
what about your server machine? is it safe? i mean no hardware problem
0
 
Mr_ShawAuthor Commented:
Here is an example of the code where i try and pull back a signle record, The first time I run it I get a record. The second time i run it I get nothing. The third time i get the record.

SELECT     *
FROM         (SELECT     EntSys,School_code,school_name,ADMISSION_DATE,HP_Type,HP_CODE,HP_Name, ROW_NUMBER() OVER (PARTITION BY EntSys
                       ORDER BY ADMISSION_DATE desc) rn
FROM         [All Schools Excluding Nurseries]) sq
WHERE     sq.RN = 1 and  HP_Type='SN' and entsys = 003
0
 
Mr_ShawAuthor Commented:
The sever is safe. The execution time is 2secs.
0
 
tigin44Commented:
try this
SELECT EntSys,School_code,school_name,ADMISSION_DATE,HP_Type,HP_CODE,HP_Name, ROW_NUMBER() OVER (PARTITION BY EntSys ORDER BY ADMISSION_DATE desc) rn
FROM  [All Schools Excluding Nurseries]
WHERE ROW_NUMBER() OVER (PARTITION BY EntSys ORDER BY ADMISSION_DATE desc) = 1 
  AND HP_Type='SN' 
  AND entsys = 003

Open in new window

0
 
Mr_ShawAuthor Commented:
Hi Tigin44,

That gave me the following error:

Msg 4108, Level 15, State 1, Line 1
Windowed functions can only appear in the SELECT or ORDER BY clauses.
0
 
tigin44Commented:
sory for my mistake this is the correct one
SELECT *
FROM   (SELECT     EntSys,School_code,school_name,ADMISSION_DATE,HP_Type,HP_CODE,HP_Name, ROW_NUMBER() OVER (PARTITION BY EntSys ORDER BY ADMISSION_DATE desc) rn
		FROM         [All Schools Excluding Nurseries]
		WHERE HP_Type='SN' AND entsys = 003 ) sq
WHERE     sq.RN = 1

Open in new window

0
 
Mr_ShawAuthor Commented:
thanks
0
 
Mr_ShawAuthor Commented:
Tiggin44 I meant to ask why this works.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

  • 7
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now