I need to find the rows that exist in one table but not in the other with condition

I need to find the rows that exist in one table but not in the other
with this condition:

(prod_name exist in table1 and not in table2.prod_name ) AND

(prod_name exist in table1 and not in table2.'S'+prod_name )

explanation:
i want to know if the product not exit and if the combination of the
charachter "S" with the product Name also not exist at the other
table

B.R
yuviSELECT prod_name
FROM table1 as A
WHERE NOT EXISTS
(select * from table2 as B
where A.prod_name = B.prod_name)
AND NOT EXISTS
(select * from table2 as C
where A.prod_name = 'S' + C.prod_name)

Roy Harvey
Beacon Falls, CT

On Wed, 20 Jun 2007 01:28:26 -0700, yuval <yuvalbra@.gmail.comwrote:

Quote:

Originally Posted by

>I need to find the rows that exist in one table but not in the other
>with this condition:
>
>(prod_name exist in table1 and not in table2.prod_name ) AND
>
>
>(prod_name exist in table1 and not in table2.'S'+prod_name )
>
>
>explanation:
>i want to know if the product not exit and if the combination of the
>charachter "S" with the product Name also not exist at the other
>table
>
>
>B.R
>yuvi

|||On Wed, 20 Jun 2007 01:28:26 -0700, yuval wrote:

Quote:

Originally Posted by

>I need to find the rows that exist in one table but not in the other
>with this condition:
>
>(prod_name exist in table1 and not in table2.prod_name ) AND
>
>
>(prod_name exist in table1 and not in table2.'S'+prod_name )
>
>
>explanation:
>i want to know if the product not exit and if the combination of the
>charachter "S" with the product Name also not exist at the other
>table

Hi yuvi,

Some alternatives for the solution posted by Roy. Try to see if they run
faster or slower on your system.

SELECT prod_name
FROM table1 AS a
WHERE NOT EXISTS
(SELECT *
FROM table2 AS b
WHERE a.prod_name IN (b.prod_name, 'S' + b.prod_name));

(Only SQL2005)

SELECT prod_name
FROM table1
EXCEPT
SELECT prod_name
FROM table2
EXCEPT
SELECT 'S' + prod_name
FROM table2;

SELECT prod_name
FROM table1
EXCEPT
(SELECT prod_name
FROM table2
UNION ALL
SELECT 'S' + prod_name
FROM table2);

(All the queries above are untested - see www.aspfaq.com/5006 if you
prefer a tested reply).

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

I need some help with update query

I have a select statement like select table1.field1, table2.field2, table 3.field3 from table1.inner join table1.field1 on table2.field2 some more joins inner join(derived table -) as newtable on some field.where ( this) and (this)I need this query to do the same thing, but I need to update the same tables and fields.I can find simple examples of inner joins but I cant piece together how to this.Can anyone just give me a basic outline of how an update would look?would I do likeupdate table1inner join table2.field1 on table1.field1 inner join table3 on table1then set table2.field1=@.var1... more sets

Ab:

You have "table1.field1, table2.field2, table3.field3" in your select list; you then say later, " ... I need to update the same tables and fields ..."

Understand, if you really are wanting to update multiple tables this cannot be done in a single update statement. Update statements can only operate on one table at a time. If you need to update three tables, you are going to need three separate updates -- each update statment corresponding to the particular table that is targeted for update. The update statements will tend to look something like

update table1
set field1 = newValue1,
field2 = newValue2,
...
fieldN = newValueN
from table1
someKindOfJoin table2
on (joinConditionsFor_2)
someKindOfJoin tableN
on (joinConditionsFor_N)
where (whereConditions)

|||I actually figured this out, but I guess there is no way to update multiple tables in 1 update query? Why can selects use multiple tables but updates cant?

I need Optimised duplicate finding query

I have a query which is like this
Select field a, field b,field c, field d from table1 where field d not in
(select distinct field d from table 2)
Table 1 has 35000 records
Table 2 has 12391876 records
Field d is of type varchar.
If there is any duplicate to be found in table 2 then the insertion from
table 1 to table 2 will not happen.
I need this query to be get optimised.Hi
DECLARE @.rowcount INT
SELECT Field1,COUNT(*) FROM Table2
GROUP BY Field1
HAVING COUNT(*)>1
SET @.rowcount =@.@.ROWCOUNT
IF @.rowcount >0 --Do exist duplicate rows
.........
"Cynthia" <Cynthia@.discussions.microsoft.com> wrote in message
news:CDCF5DF6-52FA-4D80-AAED-1F3803720972@.microsoft.com...
>I have a query which is like this
> Select field a, field b,field c, field d from table1 where field d not in
> (select distinct field d from table 2)
>
> Table 1 has 35000 records
> Table 2 has 12391876 records
> Field d is of type varchar.
>
> If there is any duplicate to be found in table 2 then the insertion from
> table 1 to table 2 will not happen.
>
> I need this query to be get optimised.
>|||I understand that the below query is used for finding out a duplicate value
in table2.
Note : field d contains <FILE NAME> . if there are 20 records in a file then
20 records will have the same filename and so on. A group of files will be
imported to the temporary table (table 1). Before inserting it to the main
table (table 2) a check is done whether that file is existing in the main
table. If it is existing then the insertion process will not done in order t
o
avoid duplicate file being entered into the main table.
We use the below said query given by me for this task.
So I need a optimised query.
"Uri Dimant" wrote:

> Hi
> DECLARE @.rowcount INT
> SELECT Field1,COUNT(*) FROM Table2
> GROUP BY Field1
> HAVING COUNT(*)>1
> SET @.rowcount =@.@.ROWCOUNT
> IF @.rowcount >0 --Do exist duplicate rows
> ..........
> "Cynthia" <Cynthia@.discussions.microsoft.com> wrote in message
> news:CDCF5DF6-52FA-4D80-AAED-1F3803720972@.microsoft.com...
>
>|||> So I need a optimised query.
CREATE indexes to optimize your query
http://www.sql-server-performance.c...red_indexes.asp
http://www.sql-server-performance.c...red_indexes.asp
http://www.sql-server-performance.c...ing_indexes.asp
"Cynthia" <Cynthia@.discussions.microsoft.com> wrote in message
news:2B4DD3B0-5BFE-4984-9726-9036457E8EA1@.microsoft.com...
>I understand that the below query is used for finding out a duplicate value
> in table2.
> Note : field d contains <FILE NAME> . if there are 20 records in a file
> then
> 20 records will have the same filename and so on. A group of files will be
> imported to the temporary table (table 1). Before inserting it to the main
> table (table 2) a check is done whether that file is existing in the main
> table. If it is existing then the insertion process will not done in order
> to
> avoid duplicate file being entered into the main table.
> We use the below said query given by me for this task.
> So I need a optimised query.
> "Uri Dimant" wrote:
>|||You might try using a not exists instead of not in. Not in will select all
12,391,876 rows from table2 then order them and summarize them, which
requires a fiar amount of in memory processing. The Not exists will check
table2 for each row in table1, meaning a maximum of 35,000 lookups. Note,
this will be faster if you have an index on table2.fieldd, but may be much
slower is this index does not exist.
Select fielda, fieldb,fieldc, fieldd from table1 t1 where not exists
(select 1 from table2 t2 where t2.fieldd = t1.fieldd)
"Cynthia" <Cynthia@.discussions.microsoft.com> wrote in message
news:CDCF5DF6-52FA-4D80-AAED-1F3803720972@.microsoft.com...
> I have a query which is like this
> Select field a, field b,field c, field d from table1 where field d not in
> (select distinct field d from table 2)
>
> Table 1 has 35000 records
> Table 2 has 12391876 records
> Field d is of type varchar.
>
> If there is any duplicate to be found in table 2 then the insertion from
> table 1 to table 2 will not happen.
>
> I need this query to be get optimised.
>

I need Help.

Hi. I'm trying a SQL like this
SELECT MIN(PRICE) FROM TABLE1 WHERE ID=112 AND STATUS=1 GROUP BY ID
its returns me
PRICE
100
150
30
But i lik SQL like this
SELECT SUM(MIN(PRICE)) FROM TABLE1 WHERE ID=112 AND STATUS=1 GROUP BY ID
i want return like
PRICE
280
When i runs second SQL an error happens.
" Cannot perform an aggregate function on an expression containing an
aggregate or a subquery. "
_____________________________________________________________
Murat FÝDANOne way
SELECT SUM(MinPrice)AS SumPrice
FROM (SELECT MIN(PRICE) AS MinPrice
FROM TABLE1
WHERE ID=3D112
AND STATUS=3D1
GROUP BY ID )x
Denis The SQL Menace
http://sqlservercode.blogspot.com
http://sqlblog.com/blogs/denis_gobo/default.aspx
On Aug 23, 9:04 am, "Murat Fidan" <murat.fi...@.tekdurak.com> wrote:
> Hi. I'm trying a SQL like this
> SELECT MIN(PRICE) FROM TABLE1 WHERE ID=3D112 AND STATUS=3D1 GROUP BY ID
> its returns me
> PRICE
> 100
> 150
> 30
> But i lik SQL like this
> SELECT SUM(MIN(PRICE)) FROM TABLE1 WHERE ID=3D112 AND STATUS=3D1 GROUP BY= ID
> i want return like
> PRICE
> 280
> When i runs second SQL an error happens.
> " Cannot perform an aggregate function on an expression containing an
> aggregate or a subquery. "
> _____________________________________________________________
> Murat F=DDDAN|||Murat Fidan,
Use a derived table.
select sum(min_price)
from
(
SELECT MIN(PRICE) as min_price
FROM TABLE1
WHERE ID=112 AND STATUS=1
GROUP BY ID
) as t
go
AMB
"Murat Fidan" wrote:
> Hi. I'm trying a SQL like this
> SELECT MIN(PRICE) FROM TABLE1 WHERE ID=112 AND STATUS=1 GROUP BY ID
> its returns me
> PRICE
> 100
> 150
> 30
> But i lik SQL like this
> SELECT SUM(MIN(PRICE)) FROM TABLE1 WHERE ID=112 AND STATUS=1 GROUP BY ID
> i want return like
> PRICE
> 280
>
> When i runs second SQL an error happens.
> " Cannot perform an aggregate function on an expression containing an
> aggregate or a subquery. "
>
> _____________________________________________________________
> Murat FÃ?DAN
>
>

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
Learn how not to multi-post to every single group and next time maybe you'll
get some help.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
"Vanessa Lee" <van77788@.yahoo.com> wrote in message
news:H4GdnZuIZaAui8LfRVn-hg@.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
>

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
See my answer in .msde.
Jacco Schalkwijk
SQL Server MVP
"Vanessa Lee" <van77788@.yahoo.com> wrote in message
news:o4SdndJqXfNEiMLfRVn-uQ@.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
>

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
Vanessa
This question has nothing to do with clustering. It looks like you have
posted to every single SQL Server newsgroup, regardless of relevancy. Please
do not do this.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Vanessa Lee" <van77788@.yahoo.com> wrote in message
news:qISdnUhUgOVTiMLfRVn-hg@.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
>

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
SELECT distinct col1, col2 FROM table1 WHERE col3 < @.V

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
Learn how not to multi-post to every single group and next time maybe you'll
get some help.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
"Vanessa Lee" <van77788@.yahoo.com> wrote in message
news:PcadnZPKVP9xiMLfRVn-jA@.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
>
|||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
"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
>
>

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
It's not completely crystal clear what you need, but my guess is:
SELECT t1.col1, t1.col2
FROM table1 t1
INNER JOIN (SELECT col1, MAX(col3) AS col3
FROM table1
WHERE col3 <= @.v
GROUP BY col1) t2
ON t1.col1 = t2.col1 AND t1.col3 = t2.col3
Jacco Schalkwijk
SQL Server MVP
"Vanessa Lee" <van77788@.yahoo.com> wrote in message
news:Q7ydnWLCvMGoi8LfRVn-iQ@.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
>

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
>> Could you help me modify the SELECT statement below that would accomplish[vbcol=seagreen]
Yes, but for such questions it is helpful to post your table structures &
sample data so that others can understand you better. For details refer to:
www.aspfaq.com/5006
[vbcol=seagreen]
Based on guesswork:
SELECT * -- use required column names
FROM tbl t1
WHERE t1.col2 = ( SELECT MAX( t2.col2 )
FROM tbl t2
WHERE t2.col1 = t1.col1
AND t2.col3 = t1.col3 )
AND t2.col3 < @.v ;
Anith

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
Vanessa,
I was going to take a look at this but I noticed that you have already
cross-posted in the programming group. Please give one group a chance before
trying another, otherwise we spend unnecessary time trying to solve
something thaat is already solved elsewhere.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

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
>

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.
VanessaVanessa,
It looks like you can't select unique values from col1 because then what
would be the value of col2 for the displayed col1?
Ex:
col1 col2
A 1
A 2
B 3
after the select you would have:
A (what value goes here, 1 or 2 ?)
B 3
You could provide one more condition for the value in field col2 (like
max(), for ex.)
select col1, max(col2) from table1 where col3 <= @.v group by col1
******
I'm afraid I don't quite get what you need when you say ' "WHERE" col3
always has
maximum value from all possible values but is <= @.V'
Just an assumption : try this
select col1, max(col2), max(col3) from table1 where col3 <= @.v group by col1
Andrei.
"Vanessa Lee" <van77788@.yahoo.com> wrote in message
news:3uKdnUXMtq0Mi8LfRVn-vg@.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
>

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.
VanessaSee my answer in .msde.
Jacco Schalkwijk
SQL Server MVP
"Vanessa Lee" <van77788@.yahoo.com> wrote in message
news:o4SdndJqXfNEiMLfRVn-uQ@.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
>

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
Learn how not to multi-post to every single group and next time maybe you'll
get some help.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
"Vanessa Lee" <van77788@.yahoo.com> wrote in message
news:KeidnUsB59hVi8LfRVn-rw@.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
>

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
>

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.
VanessaLearn how not to multi-post to every single group and next time maybe you'll
get some help.
--
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Vanessa Lee" <van77788@.yahoo.com> wrote in message
news:H4GdnZuIZaAui8LfRVn-hg@.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
>

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.
VanessaLearn how not to multi-post to every single group and next time maybe you'll
get some help.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Vanessa Lee" <van77788@.yahoo.com> wrote in message
news:PcadnZPKVP9xiMLfRVn-jA@.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
>|||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
"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
>
>