Most efficient way to solve this problem

[Space Cowboy]

SELECT top 6 odate, hid
FROM rf1
ORDER BY hid, odate DESC

I have restricted the size of this query by generation of a "supply" query rf1 and narrowed it down to 700,000 rows. Large due to varying numbers of order dates for each "hid" so have restricted to this decade.

The above select works, giving the six most recent order dates, but only on the first "hid" and not all of the order id's in the field.

 

[sonic8]

The above select works, giving the six most recent order dates, but only on the first "hid" and not all of the order id's in the field.

hid? You order by oid. The query should give you the top 6 oids and only if there are duplicates, these records will be sorted by odate.

You did not specify any problem to solve.

 

[Space Cowboy]

hid? You order by oid. The query should give you the top 6 oids and only if there are duplicates, these records will be sorted by odate.

You did not specify any problem to solve.

apologies I have edited the above.
it is only working on first hid giving the six most recent dates accurately
I need it to look at all hid in the field and give six most recent dates for each

 

[cheekybuddha]

Have a look here:

Allen Browne - Top N per Group

 

[Gasman]

Have a look here:

Allen Browne - Top N per Group

Interesting.
I was thinking of a sub query that selected all the hid's, not the other way around.

 

[Space Cowboy]

I had to terminate the query, it was running over an hour and the progress bar only had the very slightest bit of "dark grey"

 

[jdraw]

Suggest you pick a small subset of your data; put it in a separate temptable; get your query working and vet the result;
T H EN adjust your query to work with real data.
No need to test on 700K records.

 

[Space Cowboy]

Suggest you pick a small subset of your data; put it in a separate temptable; get your query working and vet the result;
T H EN adjust your query to work with real data.
No need to test on 700K records.

Thank you for that reminder, that is my normal MO. I do not know why I have changed my method for this query. Thanks

 

[ebs17]

I had to terminate the query, it was running over an hour and the progress bar only had the very slightest bit of "dark grey"

What indexes are there in your table?
700k records are a challenge, especially since a correlated subquery is used here. Without usable indexes it will be a disaster.
The connection between index and efficiency should be common knowledge.

 

[Space Cowboy]

What indexes are there in your table?
700k records are a challenge, especially since a correlated subquery is used here. Without usable indexes it will be a disaster.
The connection between index and efficiency should be common knowledge.

It is pulling data from another query RF1
I thought that hid was indexed, I have not applied any separate index as such.
I am very new to access and dont fully understand the nuances or ins and outs of it. I am trying to learn and pick it up as I go along.

 

[LarryE]

Can you post a screenshot of your table design and relationships so we can see what you actually have? The whole process starts with a proper design. If queries are taking that long, something is wrong in the initial design. I sounds like you may be trying to use ACCESS as you would a spreadsheet. You might have a lot of duplicate data somewhere.

 

[Space Cowboy]

Can you post a screenshot of your table design and relationships so we can see what you actually have? The whole process starts with a proper design. If queries are taking that long, something is wrong in the initial design. I sounds like you may be trying to use ACCESS as you would a spreadsheet. You might have a lot of duplicate data somewhere.

I wish I could, I have tried to turn the queries into tables and now they will not even run.

 

[ebs17]

I thought that hid was indexed

Faith is rarely helpful in serious situations.

If a query is used as a data source, it will be executed in addition to the new query, in the worst case multiple times instead of once due to the correlated subquery. This will increase or even duplicate the effort and runtime. Design problems contained in the query would then also be duplicated.

Therefore, I see it similarly to @LarryE: The solution to the problem starts with the tables. The indexes are defined in these (where you can look).
By knowing the query used and the database schema and the new target, one could perhaps find simpler, more direct or more efficient ways to achieve the desired result and thus perhaps avoid some problems.

 

[The_Doc_Man]

I have restricted the size of this query by generation of a "supply" query rf1 and narrowed it down to 700,000 rows.

If you are getting 700K rows from the first layer of the query, how much data do you actually have and in what kind of DB do you keep it? We need to see something about the structure and relationships because it is clear from this and your other posts that you are having major issues at every turn. I suspect that a big part of your problem is structural. An inefficient data layout will eat your lunch every time.

 

[Space Cowboy]

I have no control over the source data,
there have been two occasions in the last 10 years when there have been considerations on a redesign. On each occasion it was determined that its too big to change and too big to fail.
I have had a quick look at a couple of tables and I get a warning saying that they are indexed but there is no identifiable index key.

I think I have made real progress in a couple of weeks using access and with the help of the good people at access world, three of my base queries are running as stand alone items, and they are slowly but surely coming together to form the whole.
Is it the best way of doing things, probably not.

 

[The_Doc_Man]

it was determined that its too big to change and too big to fail.

This is the same recipe that caused the 2008 (?) near disaster of bank collapses. "Too big to fail" means that when it inevitably DOES fail, your company will go out of business.

 

[ebs17]

I have no control over the source data

Do you know which database management system the tables with data are in, or are you already rowing in the fog?

 

[LarryE]

I wish I could, I have tried to turn the queries into tables and now they will not even run.

That is not what I asked. Open the Database Relationships window. Click Database Tools>Relationships. Take a screen shot of the window contents and then copy it in here so we can look at the original design. It is obvious that the design needs revision. If you do not design it properly from the beginning, you will have nothing but problems in the future. You will spend countless hours in here asking why something is not working and why errors are occurring,

 

[Space Cowboy]

Do you know which database management system the tables with data are in, or are you already rowing in the fog?

visual foxpro

 

[Space Cowboy]

After going round in circles and crashing almost everything I have built so far I've scrapped the idea of doing the whole backend and devised an on the fly method which results in only 22,000 rows to apply my subselect query too, Whilst messing about I improved the join structure and managed to remove a couple of tables that i could have done without if I had known what I was doing this morning.
I have to keep telling myself it is only two fields, how difficult can it be.

I have had enough for 1 day and going to bed.