result data order different - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

result data order different

Author

Topic

chbala85
Starting Member

49 Posts

Posted - 2014-02-25 : 01:11:36

Select CONVERT(VARCHAR(10), enrolldate, 101) AS enrolldate,lastname ,firstname, LongDesc,Patsource,response,memberId, Case ins2 when 1 then 'Level 1' when 2 then 'Level 2' when 3 then 'Level 3' when 4 then 'Level 4' else '' end ins2,isnull(CONVERT(VARCHAR(10), DisenrolledDate, 101),'') AS DisenrolledDate From patients P , patientforms PF , Referral R ,qualifiedtype QT, (Select MAX(patientforms.patientFormId)PFID from patientforms , Referral where completed =1 and patientForms.patientFormId = Referral.patientFormId and Referral.questionId = 10829 and Referral.responseKeyDetailId = 13198 group by patientId ) SelectedPFID Where R.patientFormId = SelectedPFID .PFID And R.questionId = 10829 and R.responseKeyDetailId = 13198 And P.patientId = PF.patientId And SelectedPFID.pfid = Pf.patientFormId And QT.qualifiedTypeId = P.qualified

Select CONVERT(VARCHAR(10), enrolldate, 101) AS enrolldate,lastname ,firstname, LongDesc,Patsource,response,memberId, Case ins2 when 1 then 'Level 1' when 2 then 'Level 2' when 3 then 'Level 3' when 4 then 'Level 4' else '' end ins2,isnull(CONVERT(VARCHAR(10), DisenrolledDate, 101),'') AS DisenrolledDate From patients P , patientforms PF , Referral R ,qualifiedtype QT, (Select MAX(patientforms.patientFormId)PFID from patientforms , Referral where completed =1 and patientForms.patientFormId = Referral.patientFormId and Referral.questionId = 10829 and Referral.responseKeyDetailId = 13198 group by patientId ) SelectedPFID Where R.patientFormId = SelectedPFID .PFID And R.questionId = 10829 and R.responseKeyDetailId = 13198 And P.patientId = PF.patientId And SelectedPFID.pfid = Pf.patientFormId And QT.qualifiedTypeId = P.qualified

hi all,
i have 2 queries but both queries are same and data also getting same records.
if run two queries the result records order is different.

why the result data order is different.

please help me.

kennejd
Starting Member

11 Posts

Posted - 2014-02-26 : 12:50:14

Not sure what would cause that...maybe a new index was added? Were the queries ran against different servers...one on a production server, one against a report server? The queries do look the same.

maunishq
Yak Posting Veteran

71 Posts

Posted - 2014-02-26 : 12:59:58

Yes, it is possible. It is called Non Deterministic property. The order of data is not fixed unless it is specified by ORDER BY clause. So if you want the same order for both queries, add ORDER BY clause to both of them.

!_(M)_!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-02-27 : 02:43:35

quote:
Originally posted by chbala85

Select CONVERT(VARCHAR(10), enrolldate, 101) AS enrolldate,lastname ,firstname, LongDesc,Patsource,response,memberId, Case ins2 when 1 then 'Level 1' when 2 then 'Level 2' when 3 then 'Level 3' when 4 then 'Level 4' else '' end ins2,isnull(CONVERT(VARCHAR(10), DisenrolledDate, 101),'') AS DisenrolledDate From patients P , patientforms PF , Referral R ,qualifiedtype QT, (Select MAX(patientforms.patientFormId)PFID from patientforms , Referral where completed =1 and patientForms.patientFormId = Referral.patientFormId and Referral.questionId = 10829 and Referral.responseKeyDetailId = 13198 group by patientId ) SelectedPFID Where R.patientFormId = SelectedPFID .PFID And R.questionId = 10829 and R.responseKeyDetailId = 13198 And P.patientId = PF.patientId And SelectedPFID.pfid = Pf.patientFormId And QT.qualifiedTypeId = P.qualified

Select CONVERT(VARCHAR(10), enrolldate, 101) AS enrolldate,lastname ,firstname, LongDesc,Patsource,response,memberId, Case ins2 when 1 then 'Level 1' when 2 then 'Level 2' when 3 then 'Level 3' when 4 then 'Level 4' else '' end ins2,isnull(CONVERT(VARCHAR(10), DisenrolledDate, 101),'') AS DisenrolledDate From patients P , patientforms PF , Referral R ,qualifiedtype QT, (Select MAX(patientforms.patientFormId)PFID from patientforms , Referral where completed =1 and patientForms.patientFormId = Referral.patientFormId and Referral.questionId = 10829 and Referral.responseKeyDetailId = 13198 group by patientId ) SelectedPFID Where R.patientFormId = SelectedPFID .PFID And R.questionId = 10829 and R.responseKeyDetailId = 13198 And P.patientId = PF.patientId And SelectedPFID.pfid = Pf.patientFormId And QT.qualifiedTypeId = P.qualified

hi all,
i have 2 queries but both queries are same and data also getting same records.
if run two queries the result records order is different.

why the result data order is different.

please help me.

I dont see any ORDER BY being used. So far as you dont have an ORDER BY in query you're selecting only random records. there's no concept of default ordering of a resultset so order that you see depends on whole lot of factors and can be regarded only as random until you use ORDER BY

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources