Selecting first "x" values

[Guest]

If I have a table as follows:

ID Value
1 2
1 3
1 4
1 5
1 6
2 7
2 8
2 9
2 10


can I write a query to return the first (or last) 3 values for each ID?

I.e. if selecting first 3 values the output would be:

ID Value
1 2
1 3
1 4
2 7
2 8
2 9

If selecting the last 3 the output would be:

ID Value
1 4
1 5
1 6
2 8
2 9
2 10

I know I can return the first or last Value by for each ID Grouping by ID
and asking for Min or Max of Value but I need to return multiple Values for
each ID.

I hope this makes sense and thanks in anticipation.

 

[Guest]

Try this example

SELECT M1.Value, M1.ID
FROM MyTable AS M1
WHERE M1.Value In (SELECT Top 3 M2.Value
FROM MyTable as M2
WHERE M2.ID =M1.ID
ORDER BY M2.Value)

About selecting the top numbers or buttom numbers, just change the sort from
Asc to Desc

 

[sqlaccess]

can I write a query to return the first (or last) 3 values for each ID?

SELECT T1.ID, T1.[Value]
FROM MyTable AS T1 WHERE 3 >= (
SELECT COUNT(*)
FROM MyTable
WHERE ID = T1.ID
AND [Value] >= T1.[Value]);