473,322 Members | 1,417 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

SQL - filter out duplicates but keeping all columns

Hi guys,

I'm trying to compose a query that will select all columns from a
table, but without any duplicates.

E.g.

table name is 'tblShipment'
columns are:
fldUnique(pk) | fldShipNo | fldDate | fldValue
001 | 123 | 02/02/02 | 2000
002 | 222 | 01/01/01 | 3000
003 | 123 | 03/03/03 | 4000
004 | 444 | 04/04/04 | 5000

I want to be able to select all columns (4 columns) without
duplicates, which should give me a recordset:

fldUnique(pk) | fldShipNo | fldDate | fldValue
001 | 123 | 02/02/02 | 2000
002 | 222 | 01/01/01 | 3000
004 | 444 | 04/04/04 | 5000

** 003 is not selected becoz it's fldShipNo is identical to that of
001.

How should I go about querying that?

I've tried to select it with DISTINCT, but i'm unable to use DISTINCT
to select all columns. It is important that I have all columns
selected, especially the primary key (pk), becoz I need it for
reference.

I'm really having trouble trying to achieve this, so I hope someone
can help me out.

Thanks,
Shawn
Jul 20 '05 #1
3 12676
There may be something more elegant than this one, but it should work:

select * from tblShipment
where fldUnique not in ( select fldUnique from tblShipment group by
fldUnique having count(*)>1)
and fldShipNo not in ( select fldShipNo from tblShipment group by fldShipNo
having count(*)>1)
and fldDate not in ( select fldDate from tblShipment group by fldDate having
count(*)>1)
and fldValue not in ( select fldValue from tblShipment group by fldValue
having count(*)>1)
order by fldUnique

And since the pk is unique already, you can eliminate the first where clause
so you end up with:

select * from tblShipment
where fldShipNo not in ( select fldShipNo from tblShipment group by
fldShipNo having count(*)>1)
and fldDate not in ( select fldDate from tblShipment group by fldDate having
count(*)>1)
and fldValue not in ( select fldValue from tblShipment group by fldValue
having count(*)>1)
order by fldUnique
Jesper.

"Shawn" <sk****@hotmail.com> wrote in message
news:20*************************@posting.google.co m...
Hi guys,

I'm trying to compose a query that will select all columns from a
table, but without any duplicates.

E.g.

table name is 'tblShipment'
columns are:
fldUnique(pk) | fldShipNo | fldDate | fldValue
001 | 123 | 02/02/02 | 2000
002 | 222 | 01/01/01 | 3000
003 | 123 | 03/03/03 | 4000
004 | 444 | 04/04/04 | 5000

I want to be able to select all columns (4 columns) without
duplicates, which should give me a recordset:

fldUnique(pk) | fldShipNo | fldDate | fldValue
001 | 123 | 02/02/02 | 2000
002 | 222 | 01/01/01 | 3000
004 | 444 | 04/04/04 | 5000

** 003 is not selected becoz it's fldShipNo is identical to that of
001.

How should I go about querying that?

I've tried to select it with DISTINCT, but i'm unable to use DISTINCT
to select all columns. It is important that I have all columns
selected, especially the primary key (pk), becoz I need it for
reference.

I'm really having trouble trying to achieve this, so I hope someone
can help me out.

Thanks,
Shawn

Jul 20 '05 #2
[posted and mailed, please reply in news]

Shawn (sk****@hotmail.com) writes:
I'm trying to compose a query that will select all columns from a
table, but without any duplicates.

E.g.

table name is 'tblShipment'
columns are:
fldUnique(pk) | fldShipNo | fldDate | fldValue
001 | 123 | 02/02/02 | 2000
002 | 222 | 01/01/01 | 3000
003 | 123 | 03/03/03 | 4000
004 | 444 | 04/04/04 | 5000

I want to be able to select all columns (4 columns) without
duplicates, which should give me a recordset:

fldUnique(pk) | fldShipNo | fldDate | fldValue
001 | 123 | 02/02/02 | 2000
002 | 222 | 01/01/01 | 3000
004 | 444 | 04/04/04 | 5000

** 003 is not selected becoz it's fldShipNo is identical to that of
001.


SELECT *
FROM tblShipment t
JOIN (SELECT fldShipNo, fldUnique = MIN(fldUnique)
FROM tblShipment
GROUP BY fldShipNo) AS t1 ON t.fldUnique = t1.fldUnique

This is an untested solution. Had you included CREATE TABLE statements
for your table and your sample data as INSERT statements, you would
have gotten a tested solution.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #3
Erland Sommarskog <es****@sommarskog.se> wrote in message news:<Xn**********************@127.0.0.1>...

SELECT *
FROM tblShipment t
JOIN (SELECT fldShipNo, fldUnique = MIN(fldUnique)
FROM tblShipment
GROUP BY fldShipNo) AS t1 ON t.fldUnique = t1.fldUnique

This is an untested solution. Had you included CREATE TABLE statements
for your table and your sample data as INSERT statements, you would
have gotten a tested solution.
Thank-you Erland, your query did exactly what I wanted it to do.
I really need to learn more complex SQL queries for my future
projects. :)

"Jesper" <no@spam.com> wrote in message news:<41*********************@dread12.news.tele.dk >...
select * from tblShipment
where fldShipNo not in ( select fldShipNo from tblShipment group by
fldShipNo having count(*)>1)
and fldDate not in ( select fldDate from tblShipment group by fldDate having
count(*)>1)
and fldValue not in ( select fldValue from tblShipment group by fldValue
having count(*)>1)
order by fldUnique


Unfortunately, that didn't work, because it wasn't giving me the
record i wanted. It did not include any record with multiple identical
fields, which wasn't what I wanted.
But thanks for helping out.
Jul 20 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Joe Narissi | last post by:
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...
3
by: kjaggi | last post by:
I am trying to either write a trigger or a check constraint to prevent duplicates in my table. There are two columns I need to look at for the duplicates and only one combo value for both columns...
3
by: bellefy | last post by:
Hi All, I have a fairly large table with approx 30K rows that updates every night via a cron script that automatically downloads the 2 new csv's. The problem is the files are downloaded from...
6
by: Marlene | last post by:
Hi All I have the following scenario, where I have found all the duplicates in a table, based on an order number and a part number (item).I might have something like this: Order PODate Rec...
1
by: SPG | last post by:
Hi, I need to be able to filter the columns that are visible through a dataset. That is, I can have all columns in a dataset, but only some of them are visible when filtered. I know you can...
8
by: Mark | last post by:
Let's say I have a table of users, and each user has a list of categories. I could store each user's categories as TEXT with delimeters like "cat1|cat2|cat3" But then I need to be able to get a...
3
by: italia | last post by:
I have a database with 2 columns and more than million rows. The first column is the id Example of the data (2 columns)- 04731 CRM 04731 CRM 04731 CRM 04731 RVB 04731 RVB
0
by: Ornitobase | last post by:
Hello, my form is used to filter data displayed in its subform. The origin of the data of the subform is dynamically generated. The filters work in VBA. The code is inspired by Allen Browne's...
1
by: KimmyG | last post by:
I'm just starting to use SQL and am much more experienced in Access. Here is what I do in Access Copy a table and rename the new table "copytable" also select structure only. Open "copytable"...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.