Kay Smoljak wrote: > Sorry... just to clarify, the database structure, data in the database, > operating system, web server, and everything else I can possible think > of are identical. It's just the version of sql server that's different. > > ______________________________________________________ > Kay Smoljak Web Developer PerthWeb Pty Ltd > > Level 9/105 St George's Tc - Perth - Western Australia > Ph: (08) 9226 1366 Fax: (08) 9226 1375 > > www.perthweb.com.au developer.perthweb.com.au > > > >>-----Original Message----- >>From: Kay Smoljak [mailto:kay@;perthweb.com.au] >>Sent: Friday, 25 October 2002 11:59 AM >>To: SQL >>Subject: MS SQL 7 vs MS SQL 2000 >> >> >>Hi guys, >> >>This is driving me nuts... the following query works in MS >>SQL 7 (production server) and MS SQL 2000 (development >>server), but returns the results in different order on each. >>The results get put into a huge JavaScript object for related >>text boxes and other tricky processing, so the ordering >>really matters. It works on the production server won't work >>on the development server (wrong way around I know!), and the >>only difference is the order of the returned records - I have >>run them in side by side windows in Enterprise Manager. I >>tried adding ASC and DESC after each >>ORDER BY clause, and while it makes a difference to the >>order, the two servers are still not returning the same set. >> >>Does anyone know how I can make them return the same >>recordset in the same order? >> >>Here's the query: >>SELECT DISTINCT >> Event.pkEvent AS val1, >> Event.txtTitle AS disp1, >> EventDate.dtEvent AS disp2, >> EventDate.pkEventDate AS val2, >> TicketType.pkTicketType AS val3, >> TicketType.txtTicketType AS disp3, >> TicketType.numTicketPrice AS disp4, >> TicketType.txtTicketDesc AS disp5 >> FROM >> Event INNER JOIN EventDate ON >>Event.pkEvent = dbo.EventDate.fkEvent >> INNER JOIN EventTicketTypeLink ON >>dbo.Event.pkEvent = EventTicketTypeLink.fkEvent >> INNER JOIN TicketType ON >>EventTicketTypeLink.fkTicketType = TicketType.pkTicketType >> WHERE >> (Event.dtDeleted IS NULL) >> AND >> (EventDate.dtDeleted IS NULL) >> AND >> (TicketType.dtDeleted IS NULL) >> AND >> (EventTicketTypeLink.dtDeleted IS NULL) >> ORDER BY >> Event.txtTitle DESC, >> EventDate.dtEvent DESC, >> TicketType.txtTicketType DESC >> >> >>Thanks in advance, >>Kay.
Are the sets equal if you leave the DISTINCT? MAybe the rodering is different there already, and then a distinct could reall fuck things up. Jesse ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=6 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/sql Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
