Remove Duplicate Rows

I've got the following table data:

116525.99
116520.14
129965.03
129960.12
129967.00

And I need to write a query to return only rows 2 and 4, since the
remaining rows have duplicate IDs. I've tried the Group By, but am
having no luck.

Thanks!dale...@.gmail.com wrote:
> I've got the following table data:
> 116525.99
> 116520.14
> 129965.03
> 129960.12
> 129967.00
> And I need to write a query to return only rows 2 and 4, since the
> remaining rows have duplicate IDs. I've tried the Group By, but am
> having no luck.
> Thanks!

What do you mean by "rows 2 and 4"? Those numbers refer to positions in
the list of values you posted, but SQL Server knows nothing about that
because tables in SQL have no logical order at all. In other words you
haven't given enough information to answer your question.

If these are the only two columns you have then probably the best you
can do is:

SELECT col1, MIN(col2) AS col2
FROM your_table
GROUP BY col1 ;

or:

SELECT col1, MAX(col2) AS col2
FROM your_table
GROUP BY col1 ;

--
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/...US,SQL.90).aspx
--|||Why 2 and 4? Why not 1 & 3 or 1 & 5? What are you using as your
discriminator?

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
<dale.zjc@.gmail.com> wrote in message
news:1144699376.547275.246250@.t31g2000cwb.googlegr oups.com...
I've got the following table data:

11652 5.99
11652 0.14
12996 5.03
12996 0.12
12996 7.00

And I need to write a query to return only rows 2 and 4, since the
remaining rows have duplicate IDs. I've tried the Group By, but am
having no luck.

Thanks!|||Thanks for the quick response!

Here's my complete query:

SELECT Object.Name, Job.JobID, MAX(Data.[Value]) as NewValue,
DATEADD(S, Data.[Time], '1jan1970') AS EventDate,
Job.KSName, GETDATE() AS CURDATE

FROM DataHeader INNER JOIN
Data ON DataHeader.DataID = Data.DataID INNER JOIN
Object INNER JOIN
Job ON Object.ObjID = Job.MachineObjID ON
DataHeader.JobID = Job.JobID

Group By Job.JobID

But I'm getting the following error:

Server: Msg 8120, Level 16, State 1, Line 1
Column 'Object.Name' is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'Data.Time' is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'Job.KSName' is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause.

David Portas wrote:
> dale...@.gmail.com wrote:
> > I've got the following table data:
> > 116525.99
> > 116520.14
> > 129965.03
> > 129960.12
> > 129967.00
> > And I need to write a query to return only rows 2 and 4, since the
> > remaining rows have duplicate IDs. I've tried the Group By, but am
> > having no luck.
> > Thanks!
> What do you mean by "rows 2 and 4"? Those numbers refer to positions in
> the list of values you posted, but SQL Server knows nothing about that
> because tables in SQL have no logical order at all. In other words you
> haven't given enough information to answer your question.
> If these are the only two columns you have then probably the best you
> can do is:
> SELECT col1, MIN(col2) AS col2
> FROM your_table
> GROUP BY col1 ;
> or:
> SELECT col1, MAX(col2) AS col2
> FROM your_table
> GROUP BY col1 ;
> --
> 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/...US,SQL.90).aspx
> --|||dale...@.gmail.com wrote:
> Thanks for the quick response!
> Here's my complete query:
> SELECT Object.Name, Job.JobID, MAX(Data.[Value]) as NewValue,
> DATEADD(S, Data.[Time], '1jan1970') AS EventDate,
> Job.KSName, GETDATE() AS CURDATE
> FROM DataHeader INNER JOIN
> Data ON DataHeader.DataID = Data.DataID INNER JOIN
> Object INNER JOIN
> Job ON Object.ObjID = Job.MachineObjID ON
> DataHeader.JobID = Job.JobID
> Group By Job.JobID
> But I'm getting the following error:
> Server: Msg 8120, Level 16, State 1, Line 1
> Column 'Object.Name' is invalid in the select list because it is not
> contained in either an aggregate function or the GROUP BY clause.
> Server: Msg 8120, Level 16, State 1, Line 1
> Column 'Data.Time' is invalid in the select list because it is not
> contained in either an aggregate function or the GROUP BY clause.
> Server: Msg 8120, Level 16, State 1, Line 1
> Column 'Job.KSName' is invalid in the select list because it is not
> contained in either an aggregate function or the GROUP BY clause.

Any column that you don't want to group by needs to be enclosed in an
aggregate function (MIN or MAX for example). Your problem is obviously
a bit different to what you first asked for. The best way to post a
problem like this is to include enough code so that others can
reproduce it. See:
http://www.aspfaq.com/etiquette.asp?id=5006

--
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/...US,SQL.90).aspx
--|||(dale.zjc@.gmail.com) writes:
> Thanks for the quick response!
> Here's my complete query:
> SELECT Object.Name, Job.JobID, MAX(Data.[Value]) as NewValue,
> DATEADD(S, Data.[Time], '1jan1970') AS EventDate,
> Job.KSName, GETDATE() AS CURDATE
> FROM DataHeader INNER JOIN
> Data ON DataHeader.DataID = Data.DataID INNER JOIN
> Object INNER JOIN
> Job ON Object.ObjID = Job.MachineObjID ON
> DataHeader.JobID = Job.JobID
> Group By Job.JobID

This is possible correct version of your query, but most probably not.
It's just a piece of guesswork.

SELECT o.Name, j.JobID, mx.NewValue,
DATEADD(ss, d.[Time], '1 jan 1970') AS EventDate,
j.KSName, GETDATE() AS CURDATE
FROM (SELECT j.JobID, NewValue = MAX(d.[Value])
FROM DataHeader dh
JOIN Job j ON dh.JobID = j.JobID
JOIN Data d ON dh.DataID = d.DataID) AS mx
JOIN Job j ON mx.JobID = j.JobID
JOIN DataHeader dh ON dh.JobID = j.JobID
JOIN Data d ON dh.DataID = d.DataID
JOIN Object o ON o.ObjID = j.MachineObjID
GROUP BY j.JobID

For this type of questions it helps if you include descriptions of
your tables, including keys. Preferably in form of CREATE TABLE
statements. Sample data is also a good idea, even better if as
INSERT statements, as that makes it easy to post a tested solution.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

remove duplicate row

how do i write a query that will remove duplicate rows (rows with the same
"username" field) and keep the one with lower ID
ID USERNAME
1 john
2 john2
3 john
after executing this query this should become
ID USERNAME
1 john
2 john2
Thanks,
HowardDo:
DELETE FROM tbl
WHERE id NOT IN ( SELECT MIN( t1.id )
FROM tbl t1
WHERE t1.username = tbl.username ) ;
You can also re-write this using derived table construct or using the
EXISTS() clause.
Anith|||Hi Anith,
I'll be doing the 2005 alternatives today. ;-)
WITH Dups AS
(
SELECT *,
ROW_NUMBER() OVER(PARTITION BY username) AS rn
FROM dbo.T1
)
DELETE FROM Dups
WHERE rn > 1;
BG, SQL Server MVP
www.SolidQualityLearning.com
Join us for the SQL Server 2005 launch at the SQL W in Israel!
[url]http://www.microsoft.com/israel/sql/sqlw/default.mspx[/url]
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:uAMZ9Rd3FHA.3976@.TK2MSFTNGP15.phx.gbl...
> Do:
> DELETE FROM tbl
> WHERE id NOT IN ( SELECT MIN( t1.id )
> FROM tbl t1
> WHERE t1.username = tbl.username ) ;
> You can also re-write this using derived table construct or using the
> EXISTS() clause.
> --
> Anith
>|||I should add an ORDER BY clause...
WITH Dups AS
(
SELECT *,
ROW_NUMBER()
OVER(PARTITION BY username ORDER BY id) AS rn
FROM dbo.T1
)
DELETE FROM Dups
WHERE rn > 1;
BG, SQL Server MVP
www.SolidQualityLearning.com
Join us for the SQL Server 2005 launch at the SQL W in Israel!
[url]http://www.microsoft.com/israel/sql/sqlw/default.mspx[/url]
"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:%23vqZQ%23e3FHA.1140@.tk2msftngp13.phx.gbl...
> Hi Anith,
> I'll be doing the 2005 alternatives today. ;-)
> WITH Dups AS
> (
> SELECT *,
> ROW_NUMBER() OVER(PARTITION BY username) AS rn
> FROM dbo.T1
> )
> DELETE FROM Dups
> WHERE rn > 1;
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
> Join us for the SQL Server 2005 launch at the SQL W in Israel!
> [url]http://www.microsoft.com/israel/sql/sqlw/default.mspx[/url]
>
> "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> news:uAMZ9Rd3FHA.3976@.TK2MSFTNGP15.phx.gbl...
>

remove double value

Hy
Never use/practice SQL a lot, (vb... more, have free msde 2000) .
2 questions
A)is it simple to write a T-SQL query for having 2) at result starting
from 1) .
B)how to test dynamically sql with parmaeter ( using vb ADO)

1) before query
columA columB
d e <-same
e d <-same
e e
e d <-same

2)after query
columA columB
d e or e d
e e> A)is it simple to write a T-SQL query for having 2) at result starting
> from 1) .

SELECT DISTINCT
COALESCE(S2.a,S1.a) AS a,
COALESCE(S2.b,S1.b) AS b
FROM Sometable AS S1
LEFT JOIN Sometable AS S2
ON S1.a = S2.b AND S1.b = S2.a AND S2.a < S2.b

> B)how to test dynamically sql with parmaeter ( using vb ADO)

This may help:
http://msdn.microsoft.com/library/e...oprg02_6df7.asp
See also:
http://www.sommarskog.se/dyn-search.html

--
David Portas
----
Please reply only to the newsgroup
--|||David Portas wrote:
>>A)is it simple to write a T-SQL query for having 2) at result starting
>>from 1) .
>
> SELECT DISTINCT
> COALESCE(S2.a,S1.a) AS a,
> COALESCE(S2.b,S1.b) AS b
> FROM Sometable AS S1
> LEFT JOIN Sometable AS S2
> ON S1.a = S2.b AND S1.b = S2.a AND S2.a < S2.b
>
>>B)how to test dynamically sql with parmaeter ( using vb ADO)
>
> This may help:
> http://msdn.microsoft.com/library/e...oprg02_6df7.asp
> See also:
> http://www.sommarskog.se/dyn-search.html

hy
thank a lot for sharing your knowledge.
perhaps it was easy for you buit very hard for me
thank's for your quicky response

Remove all non digit characters from some string

Hello.
How can I write some stored procedure or function to which I will pass the
string and it will return me only numbers from my string?
Exactly I need to remove all non digit characters from some string.
Tank you.
You could write an extended stored proc in a dll...dont know if this best
solution though
"David Dvali" <david_dvali@.hotmail.com> wrote in message
news:%23iSW2D$0FHA.2348@.TK2MSFTNGP15.phx.gbl...
> Hello.
> How can I write some stored procedure or function to which I will pass the
> string and it will return me only numbers from my string?
> Exactly I need to remove all non digit characters from some string.
> Tank you.
>
|||David,
If you have a fixed set of characters to remove i.e., like with a phone
number you could do something like this:
DECLARE @.TELEPHONENUMBER VARCHAR(25)
SET @.TELEPHONENUMBER = '(503)999-1851'
SELECT REPLACE(REPLACE(REPLACE(REPLACE(@.TELEPHONENUMBER,' (',''),')',''),'
',''),'-','')
HTH
Jerry
"David Dvali" <david_dvali@.hotmail.com> wrote in message
news:%23iSW2D$0FHA.2348@.TK2MSFTNGP15.phx.gbl...
> Hello.
> How can I write some stored procedure or function to which I will pass the
> string and it will return me only numbers from my string?
> Exactly I need to remove all non digit characters from some string.
> Tank you.
>
|||Hi,
This might not be the most efficient way of doing it but it works :-)
DECLARE @.InputString varchar(32)
DECLARE @.OutputString varchar(32)
DECLARE @.i int
SET @.InputString =3D 'ABC123!=A3$456'
SET @.i =3D NULLIF(PATINDEX('%[0-9]%',@.InputString),0)
WHILE @.i IS NOT NULL
BEGIN
SET @.OutputString =3D isnull(@.OutputString,'') +
SUBSTRING(@.Inputstring, @.i, 1)
SET @.i =3D @.i +
NULLIF(PATINDEX('%[0-9]%',SUBSTRING(@.InputString,@.i+1,len(@.InputString))) ,0)
END
SELECT @.OutputString -- This gives '123456'
I would normally use a auxilary numbers table instead of the loop, but
didn't have time to go through that in this example.
Jerry Spivey wrote:[vbcol=seagreen]
> David,
> If you have a fixed set of characters to remove i.e., like with a phone
> number you could do something like this:
> DECLARE @.TELEPHONENUMBER VARCHAR(25)
> SET @.TELEPHONENUMBER =3D '(503)999-1851'
> SELECT REPLACE(REPLACE(REPLACE(REPLACE(@.TELEPHONENUMBER,' (',''),')',''),'
> ',''),'-','')
> HTH
> Jerry
> "David Dvali" <david_dvali@.hotmail.com> wrote in message
> news:%23iSW2D$0FHA.2348@.TK2MSFTNGP15.phx.gbl...
the[vbcol=seagreen]
|||This should do the trick...
DECLARE @.Index smallint
DECLARE @.SearchString varchar(25)
DECLARE @.StringLength smallint
DECLARE @.CurrentChar Char(1)
SET @.searchstring = 'ab35d0l2rle.p1C9,:$#47)('
SET @.StringLength = LEN(@.SearchString)
SET @.Index = 1
WHILE @.Index <= @.StringLength
BEGIN
SET @.CurrentChar = SUBSTRING(@.SearchString, @.index, 1)
if @.CurrentChar NOT IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9')
BEGIN
SET @.SearchString = REPLACE(@.SearchString, @.CurrentChar, 'X')
END
SET @.Index = @.Index + 1
END
SELECT REPLACE(@.SearchString, 'X','')
Returns 35021947
Replace all the non-numeric values with 'X' (so as to not screw up the WHILE
index,) then replace all Xs with an empty string. In a stored proc,
@.SearchString would be the parameter...
HTH,
Mike

Remove all non digit characters from some string

Hello.
How can I write some stored procedure or function to which I will pass the
string and it will return me only numbers from my string?
Exactly I need to remove all non digit characters from some string.
Tank you.You could write an extended stored proc in a dll...dont know if this best
solution though
"David Dvali" <david_dvali@.hotmail.com> wrote in message
news:%23iSW2D$0FHA.2348@.TK2MSFTNGP15.phx.gbl...
> Hello.
> How can I write some stored procedure or function to which I will pass the
> string and it will return me only numbers from my string?
> Exactly I need to remove all non digit characters from some string.
> Tank you.
>|||David,
If you have a fixed set of characters to remove i.e., like with a phone
number you could do something like this:
DECLARE @.TELEPHONENUMBER VARCHAR(25)
SET @.TELEPHONENUMBER = '(503)999-1851'
SELECT REPLACE(REPLACE(REPLACE(REPLACE(@.TELEPHONENUMBER,'(',''),')',''),'
',''),'-','')
HTH
Jerry
"David Dvali" <david_dvali@.hotmail.com> wrote in message
news:%23iSW2D$0FHA.2348@.TK2MSFTNGP15.phx.gbl...
> Hello.
> How can I write some stored procedure or function to which I will pass the
> string and it will return me only numbers from my string?
> Exactly I need to remove all non digit characters from some string.
> Tank you.
>|||Hi,
This might not be the most efficient way of doing it but it works :-)
DECLARE @.InputString varchar(32)
DECLARE @.OutputString varchar(32)
DECLARE @.i int
SET @.InputString =3D 'ABC123!=A3$456'
SET @.i =3D NULLIF(PATINDEX('%[0-9]%',@.InputString),0)
WHILE @.i IS NOT NULL
BEGIN
SET @.OutputString =3D isnull(@.OutputString,'') +
SUBSTRING(@.Inputstring, @.i, 1)
SET @.i =3D @.i +
NULLIF(PATINDEX('%[0-9]%',SUBSTRING(@.InputString,@.i+1,len(@.InputString))),0)
END
SELECT @.OutputString -- This gives '123456'
I would normally use a auxilary numbers table instead of the loop, but
didn't have time to go through that in this example.
Jerry Spivey wrote:
> David,
> If you have a fixed set of characters to remove i.e., like with a phone
> number you could do something like this:
> DECLARE @.TELEPHONENUMBER VARCHAR(25)
> SET @.TELEPHONENUMBER =3D '(503)999-1851'
> SELECT REPLACE(REPLACE(REPLACE(REPLACE(@.TELEPHONENUMBER,'(',''),')',''),'
> ',''),'-','')
> HTH
> Jerry
> "David Dvali" <david_dvali@.hotmail.com> wrote in message
> news:%23iSW2D$0FHA.2348@.TK2MSFTNGP15.phx.gbl...
> > Hello.
> >
> > How can I write some stored procedure or function to which I will pass =the
> > string and it will return me only numbers from my string?
> > Exactly I need to remove all non digit characters from some string.
> >
> > Tank you.
> >|||This should do the trick...
DECLARE @.Index smallint
DECLARE @.SearchString varchar(25)
DECLARE @.StringLength smallint
DECLARE @.CurrentChar Char(1)
SET @.searchstring = 'ab35d0l2rle.p1C9,:$#47)('
SET @.StringLength = LEN(@.SearchString)
SET @.Index = 1
WHILE @.Index <= @.StringLength
BEGIN
SET @.CurrentChar = SUBSTRING(@.SearchString, @.index, 1)
if @.CurrentChar NOT IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9')
BEGIN
SET @.SearchString = REPLACE(@.SearchString, @.CurrentChar, 'X')
END
SET @.Index = @.Index + 1
END
SELECT REPLACE(@.SearchString, 'X','')
Returns 35021947
Replace all the non-numeric values with 'X' (so as to not screw up the WHILE
index,) then replace all Xs with an empty string. In a stored proc,
@.SearchString would be the parameter...
HTH,
Mike

Remove all non digit characters from some string

Hello.
How can I write some stored procedure or function to which I will pass the
string and it will return me only numbers from my string?
Exactly I need to remove all non digit characters from some string.
Tank you.You could write an extended stored proc in a dll...dont know if this best
solution though
"David Dvali" <david_dvali@.hotmail.com> wrote in message
news:%23iSW2D$0FHA.2348@.TK2MSFTNGP15.phx.gbl...
> Hello.
> How can I write some stored procedure or function to which I will pass the
> string and it will return me only numbers from my string?
> Exactly I need to remove all non digit characters from some string.
> Tank you.
>|||David,
If you have a fixed set of characters to remove i.e., like with a phone
number you could do something like this:
DECLARE @.TELEPHONENUMBER VARCHAR(25)
SET @.TELEPHONENUMBER = '(503)999-1851'
SELECT REPLACE(REPLACE(REPLACE(REPLACE(@.TELEPHO
NENUMBER,'(',''),')',''),'
',''),'-','')
HTH
Jerry
"David Dvali" <david_dvali@.hotmail.com> wrote in message
news:%23iSW2D$0FHA.2348@.TK2MSFTNGP15.phx.gbl...
> Hello.
> How can I write some stored procedure or function to which I will pass the
> string and it will return me only numbers from my string?
> Exactly I need to remove all non digit characters from some string.
> Tank you.
>|||Hi,
This might not be the most efficient way of doing it but it works :-)
DECLARE @.InputString varchar(32)
DECLARE @.OutputString varchar(32)
DECLARE @.i int
SET @.InputString =3D 'ABC123!=A3$456'
SET @.i =3D NULLIF(PATINDEX('%[0-9]%',@.InputString),0)
WHILE @.i IS NOT NULL
BEGIN
SET @.OutputString =3D isnull(@.OutputString,'') +
SUBSTRING(@.Inputstring, @.i, 1)
SET @.i =3D @.i +
NULLIF(PATINDEX('%[0- 9]%',SUBSTRING(@.InputString,@.i+1,len(@.In
putString))
),0)
END
SELECT @.OutputString -- This gives '123456'
I would normally use a auxilary numbers table instead of the loop, but
didn't have time to go through that in this example.
Jerry Spivey wrote:[vbcol=seagreen]
> David,
> If you have a fixed set of characters to remove i.e., like with a phone
> number you could do something like this:
> DECLARE @.TELEPHONENUMBER VARCHAR(25)
> SET @.TELEPHONENUMBER =3D '(503)999-1851'
> SELECT REPLACE(REPLACE(REPLACE(REPLACE(@.TELEPHO
NENUMBER,'(',''),')',''),'
> ',''),'-','')
> HTH
> Jerry
> "David Dvali" <david_dvali@.hotmail.com> wrote in message
> news:%23iSW2D$0FHA.2348@.TK2MSFTNGP15.phx.gbl...
the[vbcol=seagreen]|||This should do the trick...
DECLARE @.Index smallint
DECLARE @.SearchString varchar(25)
DECLARE @.StringLength smallint
DECLARE @.CurrentChar Char(1)
SET @.searchstring = 'ab35d0l2rle.p1C9,:$#47)('
SET @.StringLength = LEN(@.SearchString)
SET @.Index = 1
WHILE @.Index <= @.StringLength
BEGIN
SET @.CurrentChar = SUBSTRING(@.SearchString, @.index, 1)
if @.CurrentChar NOT IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9')
BEGIN
SET @.SearchString = REPLACE(@.SearchString, @.CurrentChar, 'X')
END
SET @.Index = @.Index + 1
END
SELECT REPLACE(@.SearchString, 'X','')
Returns 35021947
Replace all the non-numeric values with 'X' (so as to not screw up the WHILE
index,) then replace all Xs with an empty string. In a stored proc,
@.SearchString would be the parameter...
HTH,
Mike