It is my understanding that Views cannot have parameters. Also that
stored procedures can not be queried. My problem is this:

I want to select the rows that match a certain parameter.

From that I want to select the most current 20 rows (there is a date
field).

From that I want to select the lowest 10 rows based on a numeric
field.

Finally I want that to be input to a report and some calculations.

What this basically is the selection for USGA Golf Handicap Index. It
is the most current 20 rounds of golf by a golfer, then the best 10 of
those 20 and then finally the calculation.

Any help would be appreciated.

Could be something like this. Here it is in SQL92:

SELECT S.employee_id, S.date_col, S.numeric_col
FROM Something AS S
LEFT JOIN Something AS T
ON S.employee_id = 1
AND T.employee_id = 1
AND (S.date_col < T.date_col
OR (s.date_col = T.date_col
AND S.numeric_col < T.numeric_col))
GROUP BY S.employee_id, S.date_col, S.numeric_col
HAVING COUNT(T.employee_id)<10 ;

Or, if you don't mind using the proprietary TOP modifier in SQL Server:

SELECT TOP 10 WITH TIES
employee_id, date_col, numeric_col
FROM Something
WHERE employee_id = 1
ORDER BY date_col DESC, numeric_col DESC ;

--
David Portas
SQL Server MVP
--

Do some research on "inline user defined functions". These are essentially
views with parameters.

"Jim R" <JimReid@comcast.net> wrote in message
news:7eb6df4b.0407151031.3b92ccd2@posting.google.c om...
> It is my understanding that Views cannot have parameters. Also that
> stored procedures can not be queried. My problem is this:
>
> I want to select the rows that match a certain parameter.
>
> From that I want to select the most current 20 rows (there is a date
> field).
>
> From that I want to select the lowest 10 rows based on a numeric
> field.
>
> Finally I want that to be input to a report and some calculations.
>
> What this basically is the selection for USGA Golf Handicap Index. It
> is the most current 20 rounds of golf by a golfer, then the best 10 of
> those 20 and then finally the calculation.
>
> Any help would be appreciated.

Thank you. My brother who is a dba in CA with Oracle was trying to
explain this to me but I just couldn't get it. You example allowed me
to do it both ways. I don't mind using the Top 10 with TIES but never
heard of it before.

Again thanks, makes for cleaner than temporary tables.

Jim


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!