SQL Select Statement Help

[Jet-Fighter7700]

As mentioned code academy, but I felt this one was a bit better than code academy https://www.edx.org/course/querying-transact-sql-microsoft-dat201x-0 - also free

thanks guys,

any good videos as well? any you'd recommend for somebody who has no clue about SQL server and writing queries?

 

[Shard]

thanks guys,

any good videos as well? any you'd recommend for somebody who has no clue about SQL server and writing queries?

Edx one is all video lectures with live demo's

 

[zippy]

This would still give me all the records that aren't duplicates

You would be surprised how many people don't read the spec

 

[Spacerat]

The fun stuff begins when you have to write the SQL to remove the duplicates.... NO stored procs/cursors just plain SQL

Read, steady, go...

 

[koeksGHT]

The fun stuff begins when you have to write the SQL to remove the duplicates.... NO stored procs/cursors just plain SQL

Read, steady, go...

same sql, just

delete from table where id in (SQL select)

 

[Trilkop]

The fun stuff begins when you have to write the SQL to remove the duplicates.... NO stored procs/cursors just plain SQL

Read, steady, go...

Not really, actually a very easy query:

DELETE n1 FROM [B][I]table [/I][/B]n1, [B][I]table [/I][/B]n2 WHERE n1.[B][I]id [/I][/B]< n2.[B][I]id [/I][/B]AND n1.[B][I]field [/I][/B]= n2.[B][I]field[/I][/B]

Replace the table above - id refers to your id field - replace the field. This will remove all duplicates and leave only 1.

 

[Spacerat]

Not really, actually a very easy query:

DELETE n1 FROM [B][I]table [/I][/B]n1, [B][I]table [/I][/B]n2 WHERE n1.[B][I]id [/I][/B]< n2.[B][I]id [/I][/B]AND n1.[B][I]field [/I][/B]= n2.[B][I]field[/I][/B]

Replace the table above - id refers to your id field - replace the field. This will remove all duplicates and leave only 1.

Hmm dont understand the syntax. Is this sql server? Not sure if this is std SQL. Is it like delete from table where exists?

same sql, just

delete from table where id in (SQL select)

Nope, the group by having does not give you ids

 

[poffle]

Just to confirm that the 1st answer is correct but just needs a minor tweak

SELECT Project,Task,Resource,Status, Count(*) from Table
group by Project,Task,Resource,Status
having count(*) > 1

this will show all duplicates based on the columns listed in the select. The count will give you how many records have the exact same values for Project,Task,Resource,Status

Trillkops code is pretty accurate to delete the duplicates.. self join a table.

 

[zippy]

The fun stuff begins when you have to write the SQL to remove the duplicates.... NO stored procs/cursors just plain SQL

Read, steady, go...

Oracle has a nice way of dealing with this:

 delete from $table_name where rowid in
  (
  select "rowid" from
     (select "rowid", rank_n from
         (select rank() over (partition by $primary_key order by rowid) rank_n, rowid as "rowid"
             from $table_name
             where $primary_key in
                (select $primary_key from $table_name
                  group by $all_columns
                  having count(*) > 1
                )
             )
         )
     where rank_n > 1
  )

Keeps the "first version" of the duplicates and deletes the rest

Im sure sql server also has the rank function. You can use a timestamp/date field in the order by to also rank the rows you want to discard