Query to return the TOP 25, BUT from the last records

[marabozu]

How can I make a SQL query that give the last 25 records of the table?

A Query like this: SELECT TOP 25, returns me the first 25 records, but I need (according to my request order) to get the last 25!

Any help?

M. Neto

 

[ghubbell]

Hi Marabozu,
Try changing the order your query run in i.e.: sort by orders Descending instead of Ascending. You can do this on multiple columns and the priority is left to right. That will do it! Gord
ghubbell@total.net

 

[marabozu]

ghubbell,

thank you for you attention, but I think the way that your say doesn´t work for my particular case. Imagine I want the last 5 records of this table in the field test:

1,2,3,4,5,6,7,8,9,10. If I order by test desc and get the top 5, the query result in: 10,9,8,7,6,5. But what I want is: 5,6,7,8,9,10 and I want to be able to do this in just one SQL query...

thanks anyway

M. Neto

 

[ghubbell]

M.Neto,
You can run the query as I suggested. Then create another query based on the first, and place anything back to the order you choose! Gord
ghubbell@total.net

 

[marabozu]

Gord,

Once again thank you. As I can see it's not possible to make the request in one SQL query. In this case, I'm programming using ASP and I don't know how can I create a query based on another query. Do you know how can I do that?

Thanks

M. Neto

 

[ghubbell]

M. Neto,
I hope you have tried many variations on the original query. In response to ASP: unfortunatly no. However I am sure there is someone here with either an alternative approach, or solid ASP knowledge. Keep this thread marked! Anyone? Anyone? Gord
ghubbell@total.net

 

[rafe]

M. Neto & Gord (thanks for your help btw),

Have you tried a subQuery within a query. It's all in one query but it performs both of the functions Gord describes. All I did was move it to one file. I did a mock-up & the SQL looks like...

SELECT myFile.myField FROM myFile 
WHERE  myFile.myField IN (SELECT TOP 25 myFile.DocID FROM myFile ORDER BY myFile.myField DESC)
ORDER BY myFile.myField;

the query is slow but it worked the way you wanted.

 

[tigi1]

Rafe/ghubbell

There is a related problem that that I had faced and posted in one of these forums(Access reports), for which I did not get any response. I am putting it again.

Problem relates to getting top values in a GROUP. For eg.
I have a table of monthly productions for a whole year from 200 sources. My problem is to get an oytput of the top 3 values for each month.
If I use top 3, it returns only 3 values from the whole table against 36 for the whole year.
How can this be done ?

regards and thanks in advance.
Tigi

tigilal@hotmail.com

 

[marabozu]

rafe & Gord,

the query that Rafe give me works just fine, thanks! But, belive it or not I have a problem that is similar to Tigis one. I have a db that has daily quotes, in particular it has the Open, High, Low and Close of each day. It very easy to get the High and Low for the days (because it's onluy in one record), but imagine that I want the High and Low of the week, further more, I want a table with all week highs and lows for the present year. Is it posible to do this with an SQL query? or do I have to make extra code?

once again thanks

M. Neto

 

[ghubbell]

Hello again M. Neto!
If you have a few minutes take a look at the effort that went in to Tigi's solution: Thread703-60297 . If you need to see a sample I will be pleased to give you one to test. Email & let me know 97 or 2000. Start reading!! Gord
ghubbell@total.net