Select Top 5

[LuisRibeiro]

Hello.
I have a select query that returns a list of records of the following kind:

Field1 Field2 Field3
A 8 NOK
A 7 NOK
A 5 OK
A 4 NOK
A 3 NOK
A 2 NOK
B 10 OK
B 9 NOK
B 8 NOK
B 7 NOK
B 6 NOK
B 5 NOK
.......

Is it possible to return only the top 5 Values of Field2 for each value of
Field1 where Field3 is NOK and ordered by Field2 DESC?

Thanks.

Luis

[Jim Doherty]

Hello.
I have a select query that returns a list of records of the following kind:

Field1 Field2 Field3
A 8 NOK
A 7 NOK
A 5 OK
A 4 NOK
A 3 NOK
A 2 NOK
B 10 OK
B 9 NOK
B 8 NOK
B 7 NOK
B 6 NOK
B 5 NOK
.......

Is it possible to return only the top 5 Values of Field2 for each value of
Field1 where Field3 is NOK and ordered by Field2 DESC?

Thanks.

Luis

Assuming your query is called myquery then this should return wnat you need

 
SELECT *
FROM myquery AS X
WHERE (((X.Field2) In (select top 5 field2 
		 from myquery 
		 where field1 = X.field1 and field3='NOK'
	 order by field2 desc)))
ORDER BY X.Field1, X.Field2 DESC;

Regards

Jim :)

[LuisRibeiro]

Hello. That's almost what i need but i didn't explained correctly my problem.

What i need is to return the top 5 higher values of Field2, not the 5 first records.

What changes do I need to do?

[Jim Doherty]

Hello. That's almost what i need but i didn't explained correctly my problem.

What i need is to return the top 5 higher values of Field2, not the 5 first records.

What changes do I need to do?

SELECT DISTINCT *
FROM myquery AS X
WHERE (((X.Field2) In (select DISTINCT top 5 field2
from myquery
where field1 = X.field1 and field3='NOK'
order by field2 desc)))
ORDER BY X.Field1, X.Field2 DESC;

Regards

Jim :)