f



Getting rid of duplicates

I have a table, TEST_TABLE, with 6 columns (COL1, COL2, COL3, COL4,
COL5, COL6).... I need to be able to select all columns/rows where
COL3, COL4, and COL5 are unique....

I have tried using DISTINCT and GROUP BY, but both will only allow me
to access columns COL3, COL4, and COL5..... i need access to all
columns...I just want to get rid of duplicate rows (duplicates of
COL3, COL4, and COL5)...


Thanks in advance.

Joe
0
jnarissi (1)
11/25/2003 6:50:49 PM
comp.databases.ms-sqlserver 14567 articles. 1 followers. janinepelletier (108) is leader. Post Follow

1 Replies
324 Views

Similar Articles

[PageSpeed] 35

If a row is duplicated on (col3, col4, col5) which values do you want for
col1 and col2?

Here's some example data:

CREATE TABLE Sometable (col1 INTEGER NOT NULL, col2 INTEGER NOT NULL, col3
INTEGER NOT NULL, col4 INTEGER NOT NULL, col5 INTEGER NOT NULL /* PRIMARY
KEY ??? */)

INSERT INTO Sometable VALUES (1,2,3,4,5)
INSERT INTO Sometable VALUES (2,1,3,4,5)

If you don't care which values go into col1 and col2:

SELECT MIN(col1) AS col1, MIN(col2) AS col2,
 col3, col4, col5
 FROM Sometable
 GROUP BY col3, col4, col5

If you want just one row from the table for each value of (col3, col4,
col5):

SELECT MIN(S1.col1) AS col1, S1.col2, S1.col3, S1.col4, S1.col5
 FROM Sometable AS S1
 JOIN
  (SELECT MIN(col2) AS col2, col3, col4, col5
   FROM Sometable
   GROUP BY col3, col4, col5) AS S2
  ON S1.col2 = S2.col2
   AND S1.col3 = S2.col3
   AND S1.col4 = S2.col4
   AND S1.col5 = S2.col5
 GROUP BY S1.col2, S1.col3, S1.col4, S1.col5

Try out one of these two queries. If you need more help, please post DDL and
sample data  (CREATE and INSERT statements as above) and show your required
result.

-- 
David Portas
------------
Please reply only to the newsgroup
--


0
11/25/2003 7:54:26 PM
Reply:

Similar Artilces:

Getting Rid of Duplicates
I find that I have duplicate tubes and duplicate brushes (and probably other duplicate images) in my Paint Shop Pro program files. How would I go about deleting them so I can free up some space? TIA JT "jaytea" <jayteadesigns@sbcglobal.net> wrote in message news:1166845931.027572.17520@i12g2000cwa.googlegroups.com... >I find that I have duplicate tubes and duplicate brushes (and probably > other duplicate images) in my Paint Shop Pro program files. > How would I go about deleting them so I can free up some space? > TIA > JT > Reset your cache so that it...

Getting Rid of Duplicates
I find that I have duplicate tubes and duplicate brushes (and probably other duplicate images) in my Paint Shop Pro program files. How would I go about deleting them so I can free up some space? TIA JT "jaytea" <jayteadesigns@sbcglobal.net> wrote in message news:1166845931.027572.17520@i12g2000cwa.googlegroups.com... >I find that I have duplicate tubes and duplicate brushes (and probably > other duplicate images) in my Paint Shop Pro program files. > How would I go about deleting them so I can free up some space? > TIA > JT > Reset your cache so that it...

Converting database to MS-SQLServer from PostGRESQL
Forgive me if this question is a bit too generic, if it is, feel free to just not respond. I have a database which has been running in PostgreSQL for a number of years at this stage which I want to port into MS SQL server. It seems that the SQL that Postgre outputs when I do a backup is not syntactically correct within MS-SQL server. My question is, does anyone have any documentation on how to convert a database from the Postgre platform to SQL server? Is it possible using an ODBC connection to import a database structure including table definitions, views etc into SQL Server? Failing this, does anyone have any suggestions on where I might start - I did attempt to go through the SQL code and modify it to suit SQL server, but it's about 3,500 lines of code excluding the insert statements (which themselves are also wrong) and almost every line needs something changed when comparing SQL syntax from Postgre to MSSQL server Thanks in advance for any comments/suggestions. Engada. -- Posted via a free Usenet account from http://www.teranews.com Engada wrote: > I have a database which has been running in PostgreSQL for a number of > years at this stage which I want to port into MS SQL server. > > It seems that the SQL that Postgre outputs when I do a backup is not > syntactically correct within MS-SQL server. pg_dump has a number of flags that may help, e.g. --inserts What specific types of syntax errors do you encounter? Googling (Postgr...

how to get rid of following code duplication
CL-USER> (defun range (start end) (if (<= start end) (loop for x from start upto end collecting x) (loop for x from start downto end collecting x))) On 2014-04-07, Joost Kremers <joost.m.kremers@gmail.com> wrote: > The Lisp way:[1] > > (defun my-range (start end) > (eval `(loop for x from start ,(if (<= start end) 'upto 'downto) end collecting x))) > > > JK > > > > [1] Irony alert. Well, that is certainly the NewLisp way. An equivalent solution in Tcl would also earn honest accolades in that clueless microcosm. taruss@google.com writes: > Abandoning loop: > ;; A clearer version. > ;; Slightly clever in building the list backwards. Scheme style: (defun range (start end) (if (<= start end) (range1 start end -1 #'<= nil) (range1 start end 1 #'>= nil))) (defun range1 (first last inc test acc) (if (funcall test first last) (range1 first (+ inc last) inc test (cons last acc)) acc)) Kaz Kylheku <kaz@kylheku.com> writes: > Built into TXR's Lisp dialect with auto decrement for reversed > arguments: ... http://www.nongnu.org/txr/ looks nice! But, I think the auto decrement is a misfeature because of the possibility of surprises. In Haskell of course you could write (untested) range a b | a <= b = [a..b] | otherwise = [b, b-1 .. a] ...

How to get rid of duplicates without using any group?
I have a dataset as below: serial description ---------------------- 10001 yes no may be 10001 he she them 1002 yes no may be 1002 he she them How can I get rid of additions duplicate values for the same serial? I need only one serial number ( can not do grouping it messes uo the order) as below serial description ---------------------- 10001 yes no may be he she them 1002 yes no may be he she them Thanks a lot. On 12 Jun, 00:52, need_sas_h...@YAHOO.COM (Tom Smith) wrote: > I have a dataset as below: > > serial =A0 =A0description > ---------------------- > 10001 =A0 =A0 yes > =A0 =A0 =A0 =A0 =A0 no > =A0 =A0 =A0 =A0 =A0 may be > 10001 =A0 =A0 he > =A0 =A0 =A0 =A0 =A0 she > =A0 =A0 =A0 =A0 =A0 them > 1002 =A0 =A0 =A0yes > =A0 =A0 =A0 =A0 =A0 no > =A0 =A0 =A0 =A0 =A0 may be > 1002 =A0 =A0 =A0he > =A0 =A0 =A0 =A0 =A0 she > =A0 =A0 =A0 =A0 =A0 them > > How can I get rid of additions duplicate values =A0for the same serial? > I need only one serial number ( can not do grouping it messes uo the order= ) > as below > > serial =A0 =A0description > ---------------------- > 10001 =A0 =A0 yes > =A0 =A0 =A0 =A0 =A0 no > =A0 =A0 =A0 =A0 =A0 may be > =A0 =A0 =A0 =A0 =A0 he > =A0 =A0 =A0 =A0 =...

how to get rid of following code duplication #2
CL-USER> (defun range (start end) (if (<= start end) (loop for x from start upto end collecting x) (loop for x from start downto end collecting x))) * Bigos <lhu1a6$k9l$1@speranza.aioe.org> : Wrote on Mon, 07 Apr 2014 12:14:16 +0100: | CL-USER> (defun range (start end) | (if (<= start end) | (loop for x from start upto end collecting x) | (loop for x from start downto end collecting x))) (if (<= start end) (loop for x from start upto end collecting x) (range end ...

Cleaning data
Data Cleansing: In the example (SQL Server DDL below) there are two tables - ExampleCustomer, and ExampleCar. ExampleCar is a lookup table. The ExampleCustomer table has a foreign key to the ExampleCar table. There should be two rows in there, "Ford Focus", and "Ford Galaxy". This table populates a drop-down list in the application, ordered by CarID. So a user is adding a new Customer record, selects a car from the list, and hey Presto! Someone came along and messed with the data, so now there are two rows for each car. Yes, I know I should have set the CarType column unique, but I didn't. The user has done what users always do - work around the problem. So she has added some customers using the first occurrence of the car in the drop-down list, except for the last record when she unaccountably selected the second occurrence. I have been tasked with cleaning up the data. The object is to delete all duplicate rows, but without leaving any orphaned references. At the end of the exercise the rows in the tables should be: CarID CarType 1 Ford Focus 3 Ford Galaxy PersonName CarID Adam Smith 1 Ben Smith 1 Colin Smith 3 Dave Smith 3 Edward Smith 3 Fred Smith 3 Note that the CarID for Fred Smith has been updated to the predominating row for the Ford Galaxy type. Is this clear? Can anyone see a way of doing this in a small number of queries? I could think of a way of doing it using cursors etc., but the prevailing wisdom ...

Re: How to get rid of duplicates without using any group?
On Wed, 11 Jun 2008 19:52:04 -0400, Tom Smith <need_sas_help@YAHOO.COM> wrote: >I have a dataset as below: > >serial description >---------------------- >10001 yes > no > may be >10001 he > she > them >1002 yes > no > may be >1002 he > she > them > >How can I get rid of additions duplicate values for the same serial? >I need only one serial number ( can not do grouping it messes uo the order) >as below > >serial description >---------------------- >10001 yes > no > may be > he > she > them >1002 yes > no > may be > he > she > them > >Thanks a lot. Try to maintain a distinction between the design of your data set and the designs of reports you need to produce. You need a value of SERIAL in each and every observation. If order is important for the DESCRIPTION values, and alphabetical order is not right, you need to explicitly specify that order in a separate variable. Here is your data set design: data need; input serial $ desc_order description $6.; cards; 10001 1 yes 10001 2 no 10001 3 may be 10001 4 he 10001 5 she 10001 6 them 1002 1 yes 1002 2 no 1002 3 may be 1002 4 he 1002 5 she ...

how to get the specified number of records from ms access database
Hi there, Can anybody help me in, how to get the specified number of (say 35 at a time) from ms acess database. Please let me know the steps or idea. Thanks dhaniram@gmail.com wrote: > Hi there, > > Can anybody help me in, how to get the specified number of (say 35 at a > time) from ms acess database. Please let me know the steps or idea. > > Thanks > e.g. select top 35 * from table -- [Oo=w=oO] ...

Where do I get the latest version of the MS Jet database engine?
Hi Where do I get the latest version of the MS Jet database engine? Does one have to buy the MS JET engine? Regards Kjell Arne Johansen The Jet database engine is included with Access. You get the latest version with Access 2000 or 2002, then you update it with the (seven, I think) Service Packs that apply to it. Larry Linson Microsoft Access MVP "Kjell Arne Johansen" <kjell.arne.johansen@kongsberg-simrad.com> wrote in message news:3f325273.882669171@news.eunet.no... > Hi > > Where do I get the latest version of the MS Jet database engine? > > Does one have to buy the MS JET engine? > > Regards > Kjell Arne Johansen "Kjell Arne Johansen" <kjell.arne.johansen@kongsberg-simrad.com> wrote in message news:3f325273.882669171@news.eunet.no... > Hi > > Where do I get the latest version of the MS Jet database engine? > > Does one have to buy the MS JET engine? > > Regards > Kjell Arne Johansen Go to: http://www.mvps.org/access/ HTH Frank Morse-- MVP ...

export sqlserver database to ms-access by sql script
Hi Is there anyway to export a sqlserver database to ms-access through a sql scipt only without using the sql enterprise manager. -- Message posted via SQLMonster.com http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-general/200601/1 ...

How to get MS SQL database size using API
Hi, How to get MS SQL database size using API. On 18 ao=FBt, 13:14, "mathu...@gmail.com" <mathu...@gmail.com> wrote: > =A0 =A0How to get MS SQL database size using API. Use, for example, ADO to connect to the database, like in the sample from KB220152 Then call "sp_spaceused" and the second returned value will be the database size. ...

Can't get rid of MS Intrnet Explorer
I've been plagued with trojans and adware recently, which use IE. Someone suggested uninstalling IE. I tried this but without success. When I try to uninstall using Add/Remove programs, I get a message saying "A previous version has ongoing tasks pending; a reboot is necessary before reattempting" - or words to that effect. However, when I reboot, the same thing happens. I have also tried eradicating IE, using the utility from: http://www.litepc.com/ieradicator.html but that too, failed to work. Any suggestions would be much appreciated... Allen On Thu, 18 Dec 2003 12:39:01 GMT, Allen spoketh > >I've been plagued with trojans and adware recently, which use IE. >Someone suggested uninstalling IE. I tried this but without success. >When I try to uninstall using Add/Remove programs, I get a message >saying >"A previous version has ongoing tasks pending; a reboot is necessary >before reattempting" - or words to that effect. However, when I >reboot, the same thing happens. > >I have also tried eradicating IE, using the utility from: >http://www.litepc.com/ieradicator.html >but that too, failed to work. > >Any suggestions would be much appreciated... > >Allen Quit downloading crap from the internet, and you won't have a trojan problem... Lars M. Hansen http://www.hansenonline.net (replace 'badnews' with 'news' in e-mail address) Allen wrote: > > I've been plagued wi...

Re: How to get rid of duplicates without using any group? #2
Thank you Howard. Unfortunately Proc print is not going to work for me. Either I have to create a dataset like the output or have to use proc report. Because whatever you see as the sample output will be a part of large proc report output. ...

Re: How to get rid of duplicates without using any group? #3
Tom, I agree with Howard that you really ought to reconsider your database design. However, that said, here is a way to do what I think you want: data have (drop=last_serial); infile cards missover; retain last_serial; input @1 serial $5. @11 description $6.; if serial eq last_serial then call missing(serial); if not(missing(serial)) then last_serial=serial; cards; 10001 yes no may be 10001 he she them 1002 yes no may be 1002 he she them ; HTH, Art ---------- On Wed, 11 Jun 2008 19:52:04 -0400, Tom Smith <need_sas_help@YAHOO.COM> wrote: >I have a dataset as below: > >serial description >---------------------- >10001 yes > no > may be >10001 he > she > them >1002 yes > no > may be >1002 he > she > them > >How can I get rid of additions duplicate values for the same serial? >I need only one serial number ( can not do grouping it messes uo the order) >as below > >serial description >---------------------- >10001 yes > no > may be > he > she > them >1002 yes > no > may be > he > she > them > >Thanks a lot. ...

How Do I Get Rid Of Coded Characters In An Access Database Memo Fields
The letter or symbol " � " appears in the memo field of my Access database. I tried to use search and replace to delete it, but even though I can see it the search and replace feature can not find it. It appears that the " � " is a line feed symbol because when I paste a passage into Microsoft Word it appears at the end of a line before a paragraph mark. The memo field is the passage field in my KJV Bible database, so there are over 31,000 records affected by the symbol. Below is a sample: And the earth was without form, and void; and darkness {was} � upon the face of the deep. And the Spirit of God moved upon the � face of the waters. How do I delete the symbol from the field? The database was originally prepared in Lotus Approach and converted to an Access database. Thanks in advance for any help you may be able to provide. buggabugga77@yahoo.com First, find out what Ascii character the symbol is, e.g.: ? Asc(Right("And the earth was without form, and void; and darkness {was} �",1)) You can the use the Replace function to replace it with (say) a space: Replace("And the earth was without form, and void; and darkness {was} �", Chr(236), " ") Do that in an Update query to apply it to all records in your table. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html "Bugga Bugga" <buggabugga@nomail.com> wrote in message news:tu3...

Using "uniq" or something different to get rid of duplicate lines.
Hi all, Does anyone know the best way to get rid of "duplicate" lines that have ONLY 1 field in common? My file looks something like this: aaaa,1234,cccc bbbb,1234,dddd aaaa,1234,bbbb www,1234,tttttt aaaa,2345,tttttt bbbb,2345,oooo aaaa,2345,bbbb The file is delimited using a "," and I need to leave one line only with 1234 and another line only with 2345 (it does not matter which one). Any ideas? Thanks, BTNA btna wrote: > Hi all, > > Does anyone know the best way to get rid of "duplicate" lines that > have ONLY 1 field in common? My file looks...

Get the real type of columns from MS Access databases(OLEObject, text, memo, numeric)
Hello, I made one application that opens MS Access databases.And I have one problem.How can I find the real data type of columns(text, memo, OLEObject, numeric) like it is into Access.I tried with resultSetMetaData.getColumnType(columnIndex) but I receive java.sql.Types.I have two columns that contains OLEObjects(images).But I receive two different types for columns : VARCHAR once, and for second column SMALLINT.I only want to see the types like in Access(OLEObjects, Text, Numeric, Memo...).Thanks in advance! Best regards, Calin Pop ...

HTML reprocessor: how do you get rid of bloated (obese) MS-Word (normal or filtered) HTML?
HTML reprocessor: how do you get rid of bloated (obese) MS-Word (normal or filtered) HTML? I just want all the fancy MS tags removed and replaced with basic ones, or if applicable -- stripped entirely. Style tags -- Eeek how obese these tags make HTML! Yet, most web authoring tools (the cheap or free ones) will not help with CSS. Not all my website is affected, but someday it may be. If this idea only worked for HTML ... http://hireme.geek.nz/wiki-at-home.html Most of my website code could go back to HTML 3.2, but my goal is conversion to 4.01 STRICT + UTF-8 + EN + no lo...

How to copy table from oracle database to sqlserver database ?
Hello, I need to copy a table from an 8i oracle database to a sqlserver 2000 database. Is it possible to use the command "COPY FROM ... TO ..." ? So, what is the correct syntax ? Thanks for your help Cyril On 10 Aug 2004, jewelk@free.fr wrote: > Hello, > > I need to copy a table from an 8i oracle database to a > sqlserver 2000 database. A few options exist. If this is one-off, just use sqlldr to drop the data to a file and then bcp to get it into SQLServer. > Is it possible to use the command "COPY FROM ... TO ..." ? > So, what is the correct syntax ? Well, I'm sure SQLServer has connectivity to Oracle? If you want to go this route, use that and do this from SQLServer. -- Galen Boyer On 10 Aug 2004 07:15:16 -0700, jewelk@free.fr (Cyril) wrote: >Hello, > >I need to copy a table from an 8i oracle database to a sqlserver 2000 database. > >Is it possible to use the command "COPY FROM ... TO ..." ? >So, what is the correct syntax ? > > >Thanks for your help > >Cyril Read up on using the Heterogeneous Gateway to Sqlserver. -- Sybrand Bakker, Senior Oracle DBA "Cyril" <jewelk@free.fr> wrote in message news:cd38c3d6.0408100615.6371b40e@posting.google.com... > Hello, > > I need to copy a table from an 8i oracle database to a sqlserver 2000 database. > > Is it possible to use the command "...

How to copy table from oracle database to sqlserver database ?
Hello, I need to copy a table from an 8i oracle database to a sqlserver 2000 database. Is it possible to use the command "COPY FROM ... TO ..." ? So, what is the correct syntax ? Thanks for your help Cyril "Cyril" <jewelk@free.fr> wrote in message news:cd38c3d6.0408100617.6f7b9f3e@posting.google.com... > Hello, > > I need to copy a table from an 8i oracle database to a sqlserver 2000 database. > > Is it possible to use the command "COPY FROM ... TO ..." ? > So, what is the correct syntax ? > > > Thanks for your help > > Cyril Look into linked servers in Books Online - you can create a linked server pointing to the Oracle database, then do this: insert into dbo.MSSQLTable select col1, col2, ... from Oracle..SCHEMA.TABLE Alternatively, DTS can move data from Oracle to MSSQL. Simon ...

Database Database Database Database Software Cheap
Database Database Database Database Great Datase Software See Website Below. Ultra Easy to Learn (Typically 30 Seconds) Professional Programmable Database Ver. 2.3 2.1 Million Record Capacity, (New cond). Search Rate: 2000 / Records / Second. DataBase Type: Random Access. Can Create Unlimited Databases. Programmable fields for any Application. Has Six Seperate Field Sets All Programmable. Build Time One Second, (Auto Creates DB). Setup Time: Instantly, Just Enter DB Name. Ultra Cheap Price, Special $20, Paypal Accepted. Application Mailed Instantly (file Attached Email). http://www.vehiclerepair.org/dbPro/dbpro.html ...

Database Database Database Database Software Cheap
Database Database Database Database Software Cheap Great Datase Software See Website Below. Ultra Easy to Learn (Typically 30 Seconds) Professional Programmable Database Ver. 2.3 2.1 Million Record Capacity, (New cond). Search Rate: 2000 / Records / Second. DataBase Type: Random Access. Can Create Unlimited Databases. Programmable fields for any Application. Has Six Seperate Field Sets All Programmable. Build Time One Second, (Auto Creates DB). Setup Time: Instantly, Just Enter DB Name. Ultra Cheap Price, Special $20, Paypal Accepted. Application Mailed Instantly (file Attached Email). http://www.vehiclerepair.org/dbPro/dbpro.html ...

How to get Graphs from MS Access and use them in MS Powerpoint
Is there any easy way how to use graphs created in MS Access 2000 in Powerpoint?Especially when I need to use about 20 of them. Is there any development in Office 2003? Oskar *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! Instead of creating the graphs in Access, perhaps you should use vba either (a) to open Powerpoint from within Access and create the graph by pushing data into a Powerpoint template of your own design or (b) start Powerpoint and open the Access database in code and pull the data into a graph. Either way might be quite tricky, as you'd need to know how to use vba to create a graph... which may not meet your criterion for an "easy way". But it would automate your process for 20 graphs. I don't know if you can export an Access graph; and even if you could, presumably, you'd still want to automate the process of creating your Powerpoint presentation. Regards Geoff ...

Web resources about - Getting rid of duplicates - comp.databases.ms-sqlserver

Duplicate bridge - Wikipedia, the free encyclopedia
Duplicate bridge is the most widely used variation of contract bridge in club and tournament play. It is called duplicate because the same bridge ...

Search for duplicate files - Wikipedia, the free encyclopedia
Language: English Home Random Search for duplicate files Search for duplicate files based on hash values. 900 × 600 pixel File size: 377 B MIME ...

How to Duplicate Facebook’s Hacktober
... build and maintain a security-aware culture. Director of security operations Jennifer Henley shared tips for other companies looking to duplicate ...

Facebook adds new duplicate page finder tool for admins to report unofficial pages
Some Facebook page owners are seeing a new “Report Duplicates” module that allows them to search for pages related to their own and report those ...

faroo_p2p: FAROO Search now with robust Near Duplicate Detection. Filters scraped or syndicated content ...
faroo_p2p: FAROO Search now with robust Near Duplicate Detection. Filters scraped or syndicated content. http://t.

Smart Merge - Duplicate Contacts Cleanup for AddressBook Gmail Facebook & Google contacts on the App ...
Read reviews, compare customer ratings, see screenshots, and learn more about Smart Merge - Duplicate Contacts Cleanup for AddressBook Gmail ...

Liberals to duplicate Cotter Road in $25 million plan
Duplication would close a &quot;missing link&quot; through a new 1.7 kilometre upgrade.

How-To: Safely shrink your Mac’s giant photo library, deleting duplicate images to save space
... Apple’s new Photos app , you might be surprised to learn that you’ve lost a lot of hard drive space, and that there are suddenly tons of duplicate ...

How to Fix Duplicate Content and Improve Your SEO
How to Fix Duplicate Content and Improve Your SEO Business 2 Community In 2013, Matt Cutts stated that 25% of the web was duplicate content, ...


Resources last updated: 3/22/2016 10:16:14 AM