Top 10 values in query

[Podder]

Hi,
I have performed a query and used the "Return" to give me the last 10 top values.
However, the last top value has 2 of the same e.g. 30, 30, 40, 50, 60, 70, 80, 90, 100, 110, 120
Hence, the output gives the top 11.
Is it possible to get only one of the last values when this occurs?
So it would be: 30, 40, 50, 60, 70, 80, 90, 100, 110, 120
Note: It doesnt matter which of the last 2 is used

[isladogs]

Set the query properties to Unique Values =Yes.
In SQL view this becomes SELECT DISTINCT TOP 10....

[Podder]

isladogs,
I have tried this but still gives me the top 11

Thanks

[CJ_London]

You need to show the query you are using, I suspect the only reason Colin’s solution doesn’t work is because you are bring other values through.

You are getting 11 results because you have two 30 values. So you are getting the top 10 values, just that one of them has two records with the same value. Imagine it was the last 2, which would you want to choose to leave out?

[Podder]

You need to show the query you are using, I suspect the only reason Colin’s solution doesn’t work is because you are bring other values through.

You are getting 11 results because you have two 30 values. So you are getting the top 10 values, just that one of them has two records with the same value. Imagine it was the last 2, which would you want to choose to leave out?

Ajax,
That’s what I thought, so using the top 10 won’t work in this scenario.
what if there’s a date field in the query, could it possibly be done with the two 30 values being associated with a date, where the value with the latest date is used?
So we would have the top 10 values of the field but if the last two were the same could we pick the one with the last date? Is there a criteria or formula that could be used?

[isladogs]

As already suggested, please provide your query SQL so we can see why my suggestion failed for you.
I have checked it with your data and it definitely works in my tests

[CJ_London]

You need to think this through - what if you wanted the top 10 scores/whatever and there are 15 of them all with the same value?

But since you are only giving half or less of the story can’t really advise any further

[Micron]

Likely there is more than one field in the query (e.g. autonumber) besides the one holding the "Return" field. That field will be considered in the results regardless of DISTINCT or DISTINCT ROW predicates. Copy query then remove all other query fields and use Unique Values property (set to yes) to test. You should get only 10 distinct values.

Or this might help you figure it out
http://allenbrowne.com/subquery-01.html

[Podder]

As already suggested, please provide your query SQL so we can see why my suggestion failed for you.
I have checked it with your data and it definitely works in my tests

isladogs,
Your suggestion does work, however, if I have 12 values in the query then it will leave out the duplicates and add the next ones in the list.
E.g. 20, 20, 30, 30, 40, 50, 60, 70, 80, 90, 100, 110 Shows 20, 30, 40, 50, 60, 70, 80, 90, 100, 110. So in this case it misses the 30 and adds a 20.
Maybe I didnt explain myself more clearly, sorry. What I want is the latest top 10 values, where each value is associated with a date.
In the query we have a value field with the values I have shown and a date field.
I then want the lastest 10 values but only 10 so when I have a duplicate at 10 and 11, I only want to show the latest dated one.
Im not sure the TOP process will work in this scenario, I was therefore hoping someone could point me in another direction where I could get it to work.
Sorry for the lack of clarity

[CJ_London]

see posts 4, 6 and 8.

If we don't know what you are working with, what you are trying to do, we can't help

this is my last post on the subject unless you provide the information requested

[isladogs]

@Podder
You have been asked to provide your query SQL at least 3 times but have still not done so.
Unless you provide the additional information asked for, there's nothing more I can do to assist you

[Podder]

Sorry People,
This is my SQL
SELECT DISTINCT TOP 10 SalesTBL.RepID, SalesTBL.SaleDate, SalesTBL.SaleAmount
FROM SalesTBL
ORDER BY SalesTBL.SaleAmount

NOTE: SaleAmount SORT = ASCENDING

[June7]

The 'latest 10 values' by date could all have SaleAmount of 30. It is not clear what you want.

Sample data can be helpful. Post a table of your raw data and another of desired output. Use the table tools in the advanced editor.

Maybe:

SELECT DISTINCT TOP 10 SaleAmount FROM SalesTBL;

But wouldn't 'top 10 SaleAmount' use ORDER BY SaleAmount DESCENDING?

If you want other fields in output, starts to get complicated.

SELECT TOP 10 SaleAmount, MD
FROM (SELECT SaleAmount, Max(SaleDate) AS MD FROM SalesTBL GROUP BY SaleAmount) AS Q;

or

SELECT TOP 10 SalesTBL.* FROM SalesTBL INNER JOIN (SELECT SaleAmount, Max(SaleDate) AS MD FROM SalesTBL GROUP BY SaleAmount) AS Q
ON Q.SaleAmount = SalesTbl.SaleAmount AND Q.MD = SalesTBL.SaleDate;

[Micron]

AFAIK, it is impossible to restrict a set of records to unique values if those values exist in more than one record. Each record with "30" is unique - there may be multiple values of 30 but they are still unique and as long as you include other fields you will get multiple iterations of those values. Put another way, if 2 or more records have 30 in a field but there are 2 records with 30 and (e.g.) ID autonumbers, you will get both records for 30. There are only 2 ways I know of to overcome this: create a query with ONLY the field containing the values to be part of the TOP predicate and use that in a second query. Or you can use a subquery, which are not my strong point. As I test case I included those values and autonumber ids, and to eliminate the duplicates using the subquery method I came up with a satisfactory (I think) result. Note that this was covered in my post #8. For the sake doing my own testing I came up with the below results, using the field that I wanted to get the TOP 10 from and one other field - an autonumber id. If you want to include other fields per your last post, adapt accordingly. In my test case it would not matter that I chose the MIN or MAX of id. As long as there are 2 or more records with the Return field value of 30, you will have to decide which of the other fields holds the value you want to retrieve. In the case of autonumber id I'd say it hardly matters if you use the MIN or MAX of ID. You might want to use the min or max of SaleDate.

TABLE

ReturnID	Return
1	30
2	30
3	40
4	50
5	50
6	60
7	70
8	80
9	90
10	100
11	100
12	110
13	120
14	120
15	110

SQL

SELECT tblPodder.return, max(tblPodder.ReturnID) AS maxOfID
FROM (SELECT DISTINCT Return, ReturnID 
FROM tblPodder)  AS Q
GROUP BY tblPodder.Return;

RESULT

return	maxOfID
30	2
40	3
50	5
60	6
70	7
80	8
90	9
100	11
110	15
120	14

[CJ_London]

From the OP’s comments they don’t care which records are returned, so long as there are only 10. In the initial post they would drop the 11th record. If the data happened to have records 10 and 11 with the same value, either one would do, so no logic to follow, no rule to apply

So adding an additional order by and reversing the order of selection should do the job

Select top 10 sales,id
From mytable
Order by sales,Id