Top N in View or Crystal Reports?

Question

I am wondering if it's possible to use a view to get the top 5 lines from a table. I am finding that Crystal reports doesn't seem to have anything built in to do this, or I'd do it there.

When I query the view Select * from qryTranHistory, it returns the first 5 items, but if I try to select a specific type Select * from qryTranHistory Where tID = 45 it returns nothing, since there are no tID=45 in the top 5 normally.

Is it possible to do this?
Can it be accomplished in a sub report in Crystal Reports?

Answer 1

Can you put the TOP in your SELECT statement instead of in the view?

SELECT TOP 5
    col1,
    col2,
    ...
FROM
    qryTranHistory
WHERE
    tid = 45

Answer 2

If your table has more then 5 rows I hope this query:

SELECT * FROM qryTranHistory

Returns more then 5 rows because you never mentioned TOP 5. Your question doesn't make a lot of sense as I am not sure waht you are after. You mentioned if you ran your query with WHERE tID=45, it returns nothing, what exactly do you want it to return ?

Read up on TOP in BOL:

SELECT TOP 10 Recs FROM Records WHERE...

By the way you do not want to do this in the report / a form interface, you want to do this in your db layer.

Answer 3

You can reference a sproc from Crystal Reports. In the sproc, use a conditional on the parameter.

ALTER PROCEDURE dbo.Get_TOP5
    (
    @tID INT = NULL
    )
AS
IF @tID IS NULL
    BEGIN
        SELECT TOP 5            
            FIELD1,
            FIELD2

        FROM qryTranHistory 
    END
ELSE
    BEGIN
        SELECT          
            FIELD1,
            FIELD2

        FROM qryTranHistory 

        WHERE tID =@tID
    END

Answer 4

You can do Top N processing in Crystal Reports, but it's a little obscure - you have to use the group sort expert (and in order to use that, you need to have groups and summary fields inserted into the groups.)

Doing the Top N processing in the query should be more efficient, where possible.