I need help with SELECT statement

Hi, Could you help me modify the SELECT statement below that would
accomplish my final goal.
SELECT col1, col2 FROM table1 WHERE col3 < @.V
In col1 rows have many duplicates
col2 has unique values
col3 can have 5 possible values: 5, 10, 15, 20, 25
Variable @.V can be equal to 5 or 10 or 15 or 20 or 25
The goal is to:
Return row values from col1 without duplicates, and return corresponding
values from col2 (both col1 and col2 in one row) "WHERE" col3 always has
maximum value from all possible values but is <= @.V
Thank you.
Vanessa
Not sure this is the proper forum for this question, since this forum deals
with the MS JDBC driver.
Anyhow...
If you use DISTINCT within your query, you will get all the distinct
values, but since col2 has unique values, all rows will be returned.
Since you have have a case where col1 has duplicate values, but col2 is
unique, I formulated an answer that would retrieve only the minimum value
from col2 for a given value of col1 (since you did not specify other
criteria). I then applied your criteria for col3 resulting in the following
SELECT statement:
select t1.col1, t1.col2
from #tbltemp as t1
where t1.col2 = (select min(t2.col2) from #tbltemp as t2 where t2.col1 =
t1.col1)
and t1.col3 = (select max(t3.col3) from #tbltemp as t3 where t3.col3 <=
@.v)
I believe this will give you the results for which you are looking.
Hope this helps.
"Vanessa Lee" <van77788@.yahoo.com> wrote in message
news:Aradnav_7MqAi8LfRVn-oA@.comcast.com...
> Hi, Could you help me modify the SELECT statement below that would
> accomplish my final goal.
> SELECT col1, col2 FROM table1 WHERE col3 < @.V
> In col1 rows have many duplicates
> col2 has unique values
> col3 can have 5 possible values: 5, 10, 15, 20, 25
> Variable @.V can be equal to 5 or 10 or 15 or 20 or 25
> The goal is to:
> Return row values from col1 without duplicates, and return corresponding
> values from col2 (both col1 and col2 in one row) "WHERE" col3 always has
> maximum value from all possible values but is <= @.V
> Thank you.
> Vanessa
>