SQL code

[zenn0]

WHat is the SQL code for selecting the top 3 records (out of 10 records) for every group? Let say I have 52 weeks, each week has 10 records (eg expenses),how can I select the top 3 records for
every week (week 1, week2....week52) using a single SQL statement?
It will be good if there are source code. Thanks!

[Beth Poka]

Try using the SELECT and Where clause.

SELECT select_List
FROM table_list
WHERE search_conditions

For example if you have one table called 'Weeks' containing all the data and a column name called 'RecordNumber'

use this SQL statement

SELECT *
FROM Weeks
WHERE RecordNumber < 3

Or You can use the BETWEEN instead of <, like
WHERE RecordNumber between 1 and 3

Hopes this helps

Beth

[Cakkie]

This can't be done in plain SQL, if you have SQL server, you could use cursors in a stored procedure to do that.
Otherwise, I'm affraid you will have to do it in several steps, like selecting the top 3 records for each week, week by week. One thing you can do to ease up the task is use the TOP X clause of the select statement, like SELECT TOP 3 FROM Expences, but than you can't use a group by statement, or at least not in the way you want it to.

Tom Cannaerts
slisse@planetinternet.be

Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the universe trying to produce bigger and better idiots. So far, the universe is winning -- Rich Cook