Thank you very, very much.
I got your SQL code to work.
And bonus it doesn't take too long.
I think I will create a table from this code, another table just using max to get the largest with their ids and then a unmatched query on id to get the 2nd largest values, where they exist.
Your help is greatly appreciated.
And thank you for the compliment :)
Diablette
Last Activity: May 14 '10, 01:27 PM
Joined: May 12 '10
Location:
-
Hi NeoPa,
Thank you for your reply.
So am I understanding correctly that I can't even get a table like this using SQL (i.e. with all but the top 2 values per group/postcode excluded):
The only way is doing this sql within a function:Code:Postcode Value 2600 500 2600 400 3800 600 3800 500 4460 300 4460 300 1100 100
...Code:SELECT
Leave a comment:
-
Hi Jim,
The sort of table I have (ignoring the rest of the data):
What I need to end up with somehow:...Code:Postcode Name TotalSI 2600 Place A 500 2600 Place A 400 3800 Place B 600 3800 Place B 500 3800 Place B 400 3800 Place B 300 4460 Place C 300 4460 Place C 300 4460 Place C 200 1100 Place D 100
Leave a comment:
-
Hi Jim,
I trust you will be able to help me. Thank you for taking the time to look at my query.
The original table that I am working with today has 100,000 rows.
It has 25,000 unique postcode values.
In the data I am working with today the postcodes are numbers, but other tables will have alphanumeric postcodes. There are no memo or very long string fields in the table.
(I hope I understand your question...Leave a comment:
-
Any help with this will be greatly appreciated.
I have to get it working for work, and I'm really stuck.
Thank youLeave a comment:
-
Top 2 per category
I need to get the largest and 2nd largest amounts in a column for each postcode.
I then want to add these two figures to a table which has other information for each postcode (e.g. counts, totals, etc).
I can get largest fine:
And update the table...Code:SELECT [4_digit_postcode], max([Total_SI]) AS [LargestRiskSI] INTO tLargestRiskPerPostcode FROM [Location file_DO] GROUP BY [4_digit_postcode];
No activity results to display
Leave a comment: