"Shawn" <skeetx@hotmail.com> wrote in message
news:200e4753.0411042207.ab5e042@posting.google.com...
> 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

> 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.

Erland Sommarskog <esquel@sommarskog.se> wrote in message news:<Xns9598F0626C912Yazorman@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.

"Jesper" <no@spam.com> wrote in message news:<418b7985$0$210$edfadb0f@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