Simple UPDATE TOP 1, TOP 10 and TOP 100 solution

[pete...@gmail.com]

Hello all,

I've been trying to update the top values using MS Access queries.

What I've got is the following example:

ID,HEIGHT,HIGHEST,SCORE
1,3,null,null
2,4,null,null
4,1,null,null
8,2,null,null
11,10,null,null
13,90,null,null

I need to update the TOP 1, and TOP 5 with the fact that they are the highest and I then give them a score, for example:

ID,HEIGHT,HIGHEST,SCORE
13,90,Top 1,5
11,10,Top 5,3
2,4,Top 5,3
1,3,Top 5,3
8,2,Top 5,3
4,1,null,null

Is this possible through Access queries? I tried it using the following:
Query 1:
UPDATE <TABLE> SET HIGHEST = "Top 5", SCORE = 3 WHERE EXISTS (SELECT TOP 5 <TABLE>.HEIGHT, <TABLE>.ID FROM <TABLE> ORDER BY <TABLE>.HEIGHT DESC,<TABLE>.ID ASC);
Query 2:
UPDATE <TABLE> SET HIGHEST = "Top 1", SCORE = 5 WHERE EXISTS (SELECT TOP 1 <TABLE>.HEIGHT, <TABLE>.ID FROM <TABLE> ORDER BY <TABLE>.HEIGHT DESC,<TABLE>.ID ASC);

The reason I'm also including the ID in the sort is so that I get exactly 100 rows. If I run it without it on the dataset it returns the top 202 records since they all have the same HEIGHT.

The problem is though, that if I run either of the queries, they update all the records with the same data.

Is there any solution through Access queries? I can't use anything else since I have to call upon the Access query through another piece of software.

It would be great if it could be solved in 1 query, but if not, more is fine as well.

Thanks in advance!

Kind regards,

Peter

[Patrick Finucane]

Why the where exist clause? Can't you simply update the top 5, then update the top 1 Unsure about your ref to 100 or 202 records means.

[Ulrich Möller]

I think, you should use the IN operator rather than EXISTS, properly
something like this:

UPDATE <TABLE> SET HIGHEST = "Top 5", SCORE = 3 WHERE ID IN (SELECT TOP 5 <TABLE>.ID FROM <TABLE> ORDER BY <TABLE>.HEIGHT DESC);

Ulrich

[pete...@gmail.com]

Op dinsdag 29 oktober 2013 01:23:38 UTC+1 schreef Ulrich Möller:

THanks, that worked great!