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.
VanessaIf by
"WHERE" col3 always has maximum value from all possible values but is <= @.V
do you mean Maximum vaue of Col2 from all the rows with the same value in
col1, then Try this:
SELECT col1, col2
FROM table1 T
WHERE col3 =
(Select Max(Col3)
From Table1
Where Col1 = T.col1)
And col3 < @.V
"Vanessa Lee" wrote:

> 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
>
>|||Hi,
The "possible values" can only be 5 or 10 or 15 or 20 or 25. They are from
col3 only.
Thank you.
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:108BBC29-5478-48BE-A260-8179B6EE6FC0@.microsoft.com...
> If by
> "WHERE" col3 always has maximum value from all possible values but is <=
@.V
> do you mean Maximum vaue of Col2 from all the rows with the same value in
> col1, then Try this:
> SELECT col1, col2
> FROM table1 T
> WHERE col3 =
> (Select Max(Col3)
> From Table1
> Where Col1 = T.col1)
> And col3 < @.V
>
> "Vanessa Lee" wrote:
>|||you didn't mention how to handle col2 when col1 have duplicate records,
i assume col2 and col3 has same requirement
select col1, max(col2), max(col3)
from table1
where col3 <@.V
group by col1
"Vanessa Lee" <van77788@.yahoo.com> wrote in message
news:baudnQyz7rjji8LfRVn-1w@.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
>