?
Solved

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

Posted on 2009-12-21
14
Medium Priority
?
382 Views
Last Modified: 2012-05-08
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
Comment
Question by:Mr_Shaw
  • 7
  • 5
  • 2
14 Comments
 
LVL 30

Assisted Solution

by:Reza Rad
Reza Rad earned 100 total points
ID: 26095364
is your table records growing?
It seems that your count is bigger each time,
I think some records insert into table from where
0
 
LVL 26

Expert Comment

by:tigin44
ID: 26095366
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
 

Author Comment

by:Mr_Shaw
ID: 26095380
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 26

Expert Comment

by:tigin44
ID: 26095392
do the query execution takes long? your query may be time out?
0
 

Author Comment

by:Mr_Shaw
ID: 26095405
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
 
LVL 26

Expert Comment

by:tigin44
ID: 26095418
what is the approximate query execution time?
0
 
LVL 30

Expert Comment

by:Reza Rad
ID: 26095423
what about your server machine? is it safe? i mean no hardware problem
0
 

Author Comment

by:Mr_Shaw
ID: 26095428
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
 

Author Comment

by:Mr_Shaw
ID: 26095444
The sever is safe. The execution time is 2secs.
0
 
LVL 26

Expert Comment

by:tigin44
ID: 26095456
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
 

Author Comment

by:Mr_Shaw
ID: 26095473
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
 
LVL 26

Accepted Solution

by:
tigin44 earned 1900 total points
ID: 26095507
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
 

Author Closing Comment

by:Mr_Shaw
ID: 31668493
thanks
0
 

Author Comment

by:Mr_Shaw
ID: 26095620
Tiggin44 I meant to ask why this works.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question