A query building question

Hi there,

i have a query building question and was hoping that one of you would know the answer.

Here is what i need to do :(i am using asp.net and ado.net)

I have 1 table where I store thedata, where 5 criteria determine a unique row in this table. Now, this has recently changed as the start date was added. So there potentially can be more than one entry in the table with same 5 criteria, but different start date.

I need to retrieve the row with the latest start date (currently active). The problem arises when the users enter less than 5 criteria. In this case the results may not possess same 5 criteria. Say the user searches based on 2 criteria. Then all the rows possessing these 2 ctieria will be returned, but other 3 criteria might differ with the results set.

But, i only need the latest start date row for each row. So for example, if i searched on 2 criteria, i got back 4 rows, 2 of which possess the same 5 criteria. But between these 2 i only need to display ONE row to the user - the one with the latest date.

How do i build a query? say the table name is tbl, and criteria 1 to 5 fields are called c1 ... c5, and start date field is called start_date.

thanks in advance

I am not sure I follow you all the way on this, but maybeyou could you try something like:

Select Max(start_date), c1, c2, c3, c4, c5

From tbl

Where (your where search)

Group By c1, c2, c3, c4, c5