I need help with SELECT statement

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Vanessa Lee - 15 Apr 2005 02:17 GMT

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

Reply to this Message

Adam Machanic - 15 Apr 2005 02:26 GMT

Learn how not to multi-post to every single group and next time maybe you'll
get some help.

Signature

Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--

> Hi, Could you help me modify the SELECT statement below that would
> accomplish my final goal.
[quoted text clipped - 14 lines]
> Thank you.
> Vanessa

Reply to this Message

vicky - 21 Apr 2005 23:21 GMT

Hi Vanessa,

You can try simply this statement if it does'nt matter which values from
col1 you want to take in case qualifying rows have duplicate in col1.

SELECT Col1,Col2 FROM Table1 WHERE Col1 IN
(SELECT DISTINCT Col1 FROm Table1 WHERE Col3<=@V)

We can always tweak this query to perform better as i have used IN here
instead oj join.

Thanks,

Vicky Dhawan

> Hi, Could you help me modify the SELECT statement below that would
> accomplish my final goal.
[quoted text clipped - 14 lines]
> Thank you.
> Vanessa

Reply to this Message