473,322 Members | 1,734 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Subselect Query taking a long time in SQL Server 7.0

I have a query that I need to run on a database that is on a SQL Server 7 installation. When I run the query on that database it takes forever. If I take the same query and run it on a database that is on a SQL Server 2000 installation it runs under a minute. I was wondering if someone might be able to help me redefine the query so that it works faster under SQL Server 7 but still returns the same results. The query is:

Select DISTINCT A.ID,(Select Top 1 field1 from table1 B
where B.ID=A.ID
order by field1 desc)
as field1
from table1 A
order by ID asc

There are multiple entries for the ID column in the database but what I am looking for is the most recent field1 row only so that is why the subselect. If there is another way to do it, I would be happy to try it. Thanks in advance for the help.
Jun 25 '07 #1
0 1116

Sign in to post your reply or Sign up for a free account.

Similar topics

4
by: Mike Read | last post by:
Hi I using Java and JDBC to connect to MS SQL server 2000 (using the MS drivers msbase,msutil and mssqlserver.jars). Sometimes it takes a long time for statement.executeQuery to return and...
11
by: Wolfgang Kaml | last post by:
Hello All, I have been working on this for almost a week now and I haven't anything up my sleeves anymore that I could test in addition or change.... Since I am not sure, if this is a Windows...
6
by: Muharram Mansoorizadeh | last post by:
Hi there, I've a table with 18 millions of recordes shaped like this : Code nvarchar(80) , State int , school int , class int , Term nvarchar(80) The following query takes too long to run ( more...
3
by: Neil Zanella | last post by:
Hello, I would like to ask the about the following... PostgreSQL allows tables resulting from subselects to be renamed with an optional AS keyword whereas Oracle 9 will report an error...
1
by: Mike L. Bell | last post by:
Query: update table1 t1 set end_time = ( select end_time from table2 t2 where t2.key1 = t1.key1 and t2.key2 = t1.key2 ) where exists
4
by: James | last post by:
I have a performance problem with the following query and variations on the subselect. The EXISTS version of the first example will complete in ~10 minutes. The NOT logic in both the examples...
6
by: Sebastien | last post by:
I have the following statement which I run successfully in... 1 hour 10 minutes. SELECT a.tsgicd as ACCT_ID, a.tsa5cd as SEC_ID, CASE WHEN (SUBSTRING(a.tsgicd, 6, 1) = 'R' or...
5
by: giraph | last post by:
Okay, well what my query looks like is: SELECT id, title FROM myTable LIMIT 0, 500 This query is taking a long time and changes in time each time i run it, from 0-10 seconds. And I don't...
3
by: rfuscjr via AccessMonster.com | last post by:
This is truly bizzare. I have a query that runs for hours in one Access db. When I import it into another Access db, it runs in minutes. I compacted and repaired the original, relinked tables...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.