|
hi all,
i have a sql table which contains multiple entries like 3 duplicate records.
now i have to delete 2 records out of 3 duplicates and keep only one unique record(last inserted).
can anybody pls help me in writing such a delete query...?
thanx in advance.
|
|
|
|
|
Which database product? For SQL-Server, I would use something like:
delete MyTable
from (
select DuplicateColName, Max(InsertDate) MaxInsertDate
from MyTable
group by DuplicateColName
having count(*) > 1
) as A
where MyTable.DuplicateColName = A.DuplicateColName
and MyTable.InsertDate <> A.MaxInsertDate The inline select statement identifies the duplicate records that need to be kept.
PS: You probably want to try this on a test database first.
Regards
Andy
|
|
|
|
|
|
Hi All,
What is the maximum no. rows in MS-Access2000 and MS-Access XP
Thanx
|
|
|
|
|
The max size of a single MDB file is 2GB. There is no maximum number of rows.
If you're getting into data this large, Access is not your best choice for a database. SQL Server, even the Express Edition, will handle this far more efficiently than Access could ever hope to.
|
|
|
|
|
hi,
i have a coloumn in my database which display the data as '27/01/2007'(nvarchar) . now i wanna conver this in to a Date type in SQL please tell me how to do this.
Regards
ruwandi
rkherath
|
|
|
|
|
cast(column as datetime)
Wout Louwers
|
|
|
|
|
hi,
thax for replying but the date is not typed in the proper format
user types ------> 27/05/2007 (dd/mm/yyyy)
datetime ------> 05/27/2007 (mm/dd/yyyy)
when try to do above error comes
Arithmetic overflow error converting expression to data type datetime.
plz help
regards
rkherath
|
|
|
|
|
Do you want to convert nvarchar data to datetime or vice versa?
Gautham
|
|
|
|
|
in the sql table column is nvarchar , but the text goes like 15/05/2007. but i wanna compare and filter data accoarding to the current date. current date is like 05/17/2007. so i have a problem in writing the sql SP.
basically i wanna filter all the data in the table to see only the records before 2 day from today. but table contains a nvarchar type date entered.
plz tell me a solution
regards
ruwandi
rkherath
|
|
|
|
|
as far as i understand you have a column of type nvarchar with dates in it and you want to display records with dates between today and 2 days before???
then try this:
select * from Your_TableName WHERE Your_ColumnName
BETWEEN CONVERT(NVARCHAR, GETDATE(), 103)
AND
CONVERT(NVRCHAR, GETDATE()-2, 103)
Gautham
|
|
|
|
|
wow thax!
I got it.
million thax!
brilliont idea!
thanks Gautham
regards
Ruwandi
rkherath
|
|
|
|
|
hi again,
nother small thing. is there a way that i can format the 17/05/2007(dd/mm/yyyy) in to 05/17/2007(mm/dd/yyyy)
simply what i am asking is format the date
regards
ruwandi
rkherath
|
|
|
|
|
convert(nvarchar, YourColumnName, 101)..
To know more about these formats just go to sql books online and search for Convert..
Gautham
|
|
|
|
|
i tried but the same reult comes
CONVERT(NVARCHAR,TFRTTV.OUT_DATES,101)
regards
ruwandi
rkherath
|
|
|
|
|
set dateformat 'dmy'
select convert(varchar(10),cast(columnName as datetime),101)
Regards,
Arun Kumar.A
|
|
|
|
|
No problem! You got to help me when iam stuck some where too!!
Gautham
|
|
|
|
|
Sure!
U can count on me
Regards
Ruwandi
rkherath
|
|
|
|
|
Gautham
|
|
|
|
|
Thisis when i execute the procedure , it gives me nothing but one nullvalue and the second one show me more than a 1000 records. what is the difference between these two.
"Does not show results"
declare @test varchar(15)
exec SearchGEOCode '0196','2',@test
select 'Geocode is'= @test
"Showw Resuls"
select Geocode from property
where Num_key = '0196' and Extension = 2
"the code for Procedure"
Create Procedure SearchGEOCode (@Num_key varchar(10),@Extension int,@GeoCode varchar(15) Output)
with Recompile
as
select @GeoCode= Geocode from Property
where Num_key = @num_key and Extension = @Extension
Help
Vuyiswa
|
|
|
|
|
Vuyiswamb wrote: select 'Geocode is'= @test
You are attempting to assign the value of @test to a literal. Try
SELECT 'Geocode is ' + @test
|
|
|
|
|
ok i have change like this
declare @test varchar(15)
exec SearchGEOCode '0196','2',@test
SELECT 'Geocode is ' + @test output
Results are:
--------------------------
NULL
(1 row(s) affected)
Here is the Select that returns Good results, but still i want only one Row, but even many rows come i doent matter. check this out
select Geocode from property
where Num_key = '0196' and Extension = 2
Results are:
Geocode
---------------
ETSX02
ETSX02
ETSX02
they are many, this is what i want to see in that stored Procedure. here is the code Below again
Create Procedure SearchGEOCode (@Num_key varchar(10),@Extension int,@GeoCode varchar(15) Output)
with Recompile
as
select @GeoCode= Geocode from Property
where Num_key = @num_key and Extension = @Extension
Vuyiswa
|
|
|
|
|
The output param has to be used in the exec statement, not the select statement:
declare @test varchar(15)<br />
exec SearchGEOCode '0196','2',@test output<br />
SELECT Geocode = @test
Wout Louwers
|
|
|
|
|
Thank my man you a Star
Vuyiswa
|
|
|
|
|
Vuyiswamb wrote: exec SearchGEOCode '0196','2',@test
You have to mark the @test variable as an output variable:
exec SearchGEOCode '0196', '2', @test output
But even then it will only return one value. But that is to be expected...
Wout Louwers
|
|
|
|