Field by Position

Hi, Nobody have idea to get a field in "select" statement by its position (without name then)?

My scope was to duplicate a row into a table with identity column getting all row except the identity one, that i want to provided by constant expression.

Bye and thanks...

:D :DYou will need to use dynamic SQL (constructing your SQL Statements at runtime) by referencing the database Schema or the sysobjects and syscolumns tables.|||Uhmmm...why do you need to do this...?|||Often I have duplicate row in table with a lot of columns ... and i wanted a statement to avoid to call each time columns by name ...

The real scope was to find a statement to say : "Get all the columns except primary key ... that I provide by constant expression ...."

Maybe the unique solution is to prepare a program to write statement how i need

Thanks|||why not just write the query with all the column names except the primary key?

that would've taken no more than a minute or two, a lot faster than writing a query to go against the system tables, and a lot faster than writing a program to do that

Fetching duplicate rows uisng IN clause

I have two rows in table1 and these two rows are then duplicated in
table2 making four rows. I then try and use the following query to
select the four rows from table2.
select * from Product P
where P.ProductVersionID in (select ProductVersionID from PartSet where
SetID = ?)
Running the above query only return the two rows from Product instead
of the four rows that exist in SetPart.
Now if I run the query below I get back the four rows that I want.
select * from Product P
inner join SetPart SP on P.ProductVersionID = SP.ProductVersionID where
SP.SetID = ?
Can someone please explain to me why the query with the IN clause
doesn't return the four rows that I want?
DML:
CREATE TABLE SetPart (
SetID int NOT NULL,
ProductVersionID int NOT NULL,
ProductTypeID int NOT NULL
)
GO
CREATE TABLE Product (
ProductVersionID int NOT NULL,
ProductName varchar (20) NOT NULL
)
GOmohaaron@.gmail.com wrote:
> I have two rows in table1 and these two rows are then duplicated in
> table2 making four rows. I then try and use the following query to
> select the four rows from table2.
> select * from Product P
> where P.ProductVersionID in (select ProductVersionID from PartSet where
> SetID = ?)
> Running the above query only return the two rows from Product instead
> of the four rows that exist in SetPart.
> Now if I run the query below I get back the four rows that I want.
> select * from Product P
> inner join SetPart SP on P.ProductVersionID = SP.ProductVersionID where
> SP.SetID = ?
> Can someone please explain to me why the query with the IN clause
> doesn't return the four rows that I want?
Because IN is not a JOIN. IN just determines whether a row (or rows)
exists in the subquery. Apparently you want the join instead.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||>> Can someone please explain to me why the query with the IN clause doesn't
IN() implies only the distinct rows in a subquery result. It is different
from an INNER JOIN which returns all rows based on the column used in the
join. If the columns participating in the INNER JOIN are not unique
duplicates can occur in the result.
In general, keyless tables are practically useless and it is recommended
that every table must have a column or set of columns that can uniquely
identify a row in the table.
Anith

Fetching Duplicate Rows

Hi,

I have some duplicate rows in a table. I didnt define any primary key or unique key on the table.

I can get unique rows using DISTINCT, but i want to fetch only the duplicated rows and also i want to delete the duplicated rows.

How can i do it?

Please help me....

Thanx in AdvanceI have some duplicate rows in a table. I didnt define any primary key or unique key on the table.

Can you paste some DDL. Are all the columns for the duplicated rows same ? Is there any date time stamp ? You could find some help Here (http://www.sqlteam.com/item.asp?ItemID=3331)|||Try something like:

Select * from Table
Where {Key Fields} IN
(select {key Fields} from Table
group by {Key Fields}
Having Count(*) >1)

Where Table is your Table/Query and {Key Fields} is the list of fields you want to search for duplicates on

HTH

Marp

[
QUOTE]Originally posted by d_kishan
Hi,

I have some duplicate rows in a table. I didnt define any primary key or unique key on the table.

I can get unique rows using DISTINCT, but i want to fetch only the duplicated rows and also i want to delete the duplicated rows.

How can i do it?

Please help me....

Thanx in Advance [/QUOTE]|||You say you want to delete the duplicated rows, but I bet what you really want is to delete all but one of each duplicated row. Your first step is to add a primary key. Otherwise, there is no way to discern which of the duplicated rows to retain and your only option will be to select DISTINCT into a new table and then replace your old table.|||If you want to know how many duplicate rows you have per your Key criteria,

do this

[Select Key1,Key2,..Keyn, count(*)
from Table
group by {Key1,Key2,...Keyn}
Having Count(*) >1)

Fetch Returning Duplicate Last Record

Ok, this thing is returning the last record twice. If I have only one record it returns it twice, multiple records gives me the last one twice. I am sure some dumb pilot error is involved, HELP!

Thanks in advance, Larry

ALTER FUNCTION dbo.TestFoodDisLikes

(

@.ResidentID int

)

RETURNS varchar(250)

AS

BEGIN

DECLARE @.RDLike varchar(50)

DECLARE @.RDLikeList varchar(250)

BEGIN

SELECT @.RDLikeList = ''

DECLARE RDLike_cursor CURSOR

LOCAL SCROLL STATIC

FOR

SELECT FoodItem

FROM tblFoodDislikes

WHERE (ResidentID = @.ResidentID) AND (Breakfast = 'True')

OPEN RDLike_cursor

FETCH NEXT FROM RDLike_cursor

INTO @.RDLike

SELECT @.RDLikeList = @.RDLike

WHILE @.@.FETCH_STATUS = 0

BEGIN

FETCH NEXT FROM RDLike_cursor

INTO @.RDLike

SELECT @.RDLikeList = @.RDLikeList + ', ' + @.RDLike

END

CLOSE RDLike_cursor

DEALLOCATE RDLike_cursor

END

RETURN @.RDLikeList

END

Your selecting into the cursor before any operation in the begin statement. ****

ALTER FUNCTION dbo.TestFoodDisLikes
(
@.ResidentID int
)
RETURNS varchar(250)
AS
BEGIN
DECLARE @.RDLike varchar(50)
DECLARE @.RDLikeList varchar(250)
BEGIN
SELECT @.RDLikeList = ''
DECLARE RDLike_cursor CURSOR
LOCAL SCROLL STATIC
FOR
SELECT FoodItem
FROM tblFoodDislikes
WHERE (ResidentID = @.ResidentID) AND (Breakfast = 'True')
OPEN RDLike_cursor
FETCH NEXT FROM RDLike_cursor
INTO @.RDLike
SELECT @.RDLikeList = @.RDLike
WHILE @.@.FETCH_STATUS = 0
BEGIN --****

SELECT @.RDLikeList = @.RDLikeList + ', ' + @.RDLike--****
FETCH NEXT FROM RDLike_cursor INTO @.RDLike--****

END--****
CLOSE RDLike_cursor
DEALLOCATE RDLike_cursor
END
RETURN @.RDLikeList
END

|||

Steve,

It still duplicates one record, that just moved it to the beginning. If I am expecting to see somethimg like 'Ham, Beans, Biscuit, Apples, Beets' I get 'Ham, Ham, Beans, Biscuit, Apples, Beets'. Looks like the second part of the FETCH is starting all over again

|||Actually I would avoid using a cursor for this, according to the Northwind database it would be something like this:

DECLARE @.Cities VARCHAR(8000)

SET @.Cities = ''

SELECT @.Cities = CASE @.Cities

WHEN '' THEN City

ELSE @.Cities + ', ' + City

END

from CUstomers

Group by City

Select @.Cities

According to your problem it would be

DECLARE @.ResidentID VARCHAR(8000)

DECLARE @.FoodItems VARCHAR(8000)

SET @.FoodItems = ''

SELECT @.FoodItems =CASE @.FoodItems

WHEN '' THEN FoodItem

ELSE @.FoodItems + ', ' + FoodItem

END

from tblFoodDislikes

WHERE (ResidentID = @.ResidentID) AND (Breakfast = 'True')

Group by FoodItem

Select @.FoodItems

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks Jens, That works great and I did use it, seems faster. I am still at a loss as to why the fetch was returning a duplicate record but I like this better.

Larry

Faster Remove Duplicate SQL

I have a table containing over 100,000 email addresses. This email table gets duplicates in it, and our customers don't want a second (or third or fourth) copy of our news letter. To prevent this, we run the following SQL to kill the duplicates:

Code Snippet

DELETE FROM _email WHERE _email.eid IN
(
SELECT tbl1.eid FROM _email AS tbl1 WHERE Exists
(
SELECT emailaddress, Count(eid) FROM _email WHERE _email.emailaddress = tbl1.emailaddress GROUP BY _email.emailaddress HAVING Count(_email.eid) > 1
)
)
AND _email.eid NOT IN
(
SELECT Min(eid) FROM _email AS tbl1 WHERE Exists
(
SELECT emailaddress, Count(eid) FROM _email WHERE _email.emailaddress = tbl1.emailaddress GROUP BY _email.emailaddress HAVING Count(_email.eid) > 1
)
GROUP BY emailaddress
);

This query takes about 2hrs to run which is really hurting our server preformance. Is there any way to do this faster?

I am running SQL Server 2000

Thanks in advance

Create a unique non clustered index and turn on IGNORE_DUP_KEY after you run your code to delete all the existing duplicates. Then SQL Server will not insert duplicates to the column, try the link below for index options.

http://msdn2.microsoft.com/en-us/library/ms186869.aspx

|||

You can try the following queries,

Code Snippet

delete from _email

from _email A

join (select min(eid) eid,email from _email group by email) B

on A.email = B.email

Where

b.eid <> a.eid

Code Snippet

delete from _email

from _email E

Where exists

(

select 1 from _email A

join (select min(eid) eid,email from _email group by email) B

on A.email = B.email

Where

b.eid <> a.eid

and E.eid=A.eid

)

|||

If you are using SS 2005, then try:

;with cte

as

(

select *, row_number() over(partition by emailaddress order by eid ASC) as rn

from _email

)

delete cte

where rn > 1;

AMB