USE OF DISTINCT COMMAND IN MS QUERY

Hi,
I wanted to use Distinct Command thro MS query (excel)for filtering
duplication from other database. PLs confirm how i can use & syntax for the
same.

I have tried "SELECT DISTINCT FIELD1,FIELD2,..... FROM TABLE"

Regards

Have you tried

select field1,field2,.....
from tablename
group by field1, field2,....

Tim


"Prakash" <Prakash@discussions.microsoft.com> wrote in message
news:6B564B61-B19F-403F-BAC2-D45794FE44A0@microsoft.com...
> Hi,
> I wanted to use Distinct Command thro MS query (excel)for filtering
> duplication from other database. PLs confirm how i can use & syntax for
> the
> same.
>
> I have tried "SELECT DISTINCT FIELD1,FIELD2,..... FROM TABLE"
>
> Regards

I tried, but no result. It could not filter duplicate entries. i need to
filter only 1st row of the duplicate entry.

"Tim Williams" wrote:

> Have you tried
>
> select field1,field2,.....
> from tablename
> group by field1, field2,....
>
> Tim
>
>
> "Prakash" <Prakash@discussions.microsoft.com> wrote in message
> news:6B564B61-B19F-403F-BAC2-D45794FE44A0@microsoft.com...
> > Hi,
> > I wanted to use Distinct Command thro MS query (excel)for filtering
> > duplication from other database. PLs confirm how i can use & syntax for
> > the
> > same.
> >
> > I have tried "SELECT DISTINCT FIELD1,FIELD2,..... FROM TABLE"
> >
> > Regards
>
>
>

What database are you using and do you have a query which works when run in
another query tool?
How many column/what types etc...?

Tim

"Prakash" <Prakash@discussions.microsoft.com> wrote in message
news:5DD3F79F-A3C5-461C-B379-6EF4B221EC54@microsoft.com...
>I tried, but no result. It could not filter duplicate entries. i need to
> filter only 1st row of the duplicate entry.
>
> "Tim Williams" wrote:
>
>> Have you tried
>>
>> select field1,field2,.....
>> from tablename
>> group by field1, field2,....
>>
>> Tim
>>
>>
>> "Prakash" <Prakash@discussions.microsoft.com> wrote in message
>> news:6B564B61-B19F-403F-BAC2-D45794FE44A0@microsoft.com...
>> > Hi,
>> > I wanted to use Distinct Command thro MS query (excel)for filtering
>> > duplication from other database. PLs confirm how i can use & syntax for
>> > the
>> > same.
>> >
>> > I have tried "SELECT DISTINCT FIELD1,FIELD2,..... FROM TABLE"
>> >
>> > Regards
>>
>>
>>

Thanks for you suggetion.
I am using AS400 database files, one file contains around 1500 rows & 15
columns & in the row, nearly 500 rows are duplicate (same data repeating), so
i wanted to filter while downloading thro MS query. Blw i gave the SQL
statement i have written
(SELECT ITH.TCOM, ITH.TTYPE, ITH.TWHS, ITH.THRNO, IIM.IITYP, ITH.TTDTE,
IIM.IPURC, ITH.TREF, AVM.VENDOR, AVM.VNDNAM, ITH.THCURR, HPOX.POGSPR,
ITH.TPROD, IIM.IDESC, ITH.TQTY, ITH.THLIN, IIMX.IXLOC1, ITH.TLOCT, AVM.VTERMS
FROM AMPRDB.AMP40LPF.AVM AVM, AMPRDB.AMP40LPF.HPOX HPOX, AMPRDB.AMP40LPF.IIM
IIM, AMPRDB.AMP40LPF.IIMX IIMX, AMPRDB.AMP40LPF.ITH ITH
WHERE ITH.TPROD = HPOX.PPRODX AND ITH.TREF = HPOX.PORDX AND ITH.TVEND =
AVM.VENDOR AND ITH.THLIN = HPOX.PLINEX AND ITH.TPROD = IIM.IPROD AND
HPOX.PPRODX = IIM.IPROD AND IIM.IPROD = IIMX.IXPROD AND ((ITH.TTDTE Between
20060304 And 20060331) AND (ITH.TTYPE In ('RP','RS','N')) AND
(ITH.THCURR<>'INR'))
GROUP BY ITH.TCOM, ITH.TTYPE, ITH.TWHS, ITH.THRNO, IIM.IITYP, ITH.TTDTE,
IIM.IPURC, ITH.TREF, AVM.VENDOR, AVM.VNDNAM, ITH.THCURR, HPOX.POGSPR,
ITH.TPROD, IIM.IDESC, ITH.TQTY, ITH.THLIN, IIMX.IXLOC1, ITH.TLOCT, AVM.VTERMS
ORDER BY ITH.TTDTE)

i need to apply distinct for ITH.TCOM field from ITH file.


"Tim Williams" wrote:

> What database are you using and do you have a query which works when run in
> another query tool?
> How many column/what types etc...?
>
> Tim
>
> "Prakash" <Prakash@discussions.microsoft.com> wrote in message
> news:5DD3F79F-A3C5-461C-B379-6EF4B221EC54@microsoft.com...
> >I tried, but no result. It could not filter duplicate entries. i need to
> > filter only 1st row of the duplicate entry.
> >
> > "Tim Williams" wrote:
> >
> >> Have you tried
> >>
> >> select field1,field2,.....
> >> from tablename
> >> group by field1, field2,....
> >>
> >> Tim
> >>
> >>
> >> "Prakash" <Prakash@discussions.microsoft.com> wrote in message
> >> news:6B564B61-B19F-403F-BAC2-D45794FE44A0@microsoft.com...
> >> > Hi,
> >> > I wanted to use Distinct Command thro MS query (excel)for filtering
> >> > duplication from other database. PLs confirm how i can use & syntax for
> >> > the
> >> > same.
> >> >
> >> > I have tried "SELECT DISTINCT FIELD1,FIELD2,..... FROM TABLE"
> >> >
> >> > Regards
> >>
> >>
> >>
>
>
>

Looks fine to me. I don't know why it wouldn't produce only distinct rows.

You say you want only the "first row" of duplicate entries: if all selected
columns have the same values in multiple rows then this should do it.

Perhaps it might help to first simplify your query to use the smallest
number of fields which still reproduces the problem.

--
Tim Williams
Palo Alto, CA


"Prakash" <Prakash@discussions.microsoft.com> wrote in message
news:71D91497-888D-47F0-99B6-0D9BB45D12B7@microsoft.com...
> Thanks for you suggetion.
> I am using AS400 database files, one file contains around 1500 rows & 15
> columns & in the row, nearly 500 rows are duplicate (same data repeating),
so
> i wanted to filter while downloading thro MS query. Blw i gave the SQL
> statement i have written
> (SELECT ITH.TCOM, ITH.TTYPE, ITH.TWHS, ITH.THRNO, IIM.IITYP, ITH.TTDTE,
> IIM.IPURC, ITH.TREF, AVM.VENDOR, AVM.VNDNAM, ITH.THCURR, HPOX.POGSPR,
> ITH.TPROD, IIM.IDESC, ITH.TQTY, ITH.THLIN, IIMX.IXLOC1, ITH.TLOCT,
AVM.VTERMS
> FROM AMPRDB.AMP40LPF.AVM AVM, AMPRDB.AMP40LPF.HPOX HPOX,
AMPRDB.AMP40LPF.IIM
> IIM, AMPRDB.AMP40LPF.IIMX IIMX, AMPRDB.AMP40LPF.ITH ITH
> WHERE ITH.TPROD = HPOX.PPRODX AND ITH.TREF = HPOX.PORDX AND ITH.TVEND =
> AVM.VENDOR AND ITH.THLIN = HPOX.PLINEX AND ITH.TPROD = IIM.IPROD AND
> HPOX.PPRODX = IIM.IPROD AND IIM.IPROD = IIMX.IXPROD AND ((ITH.TTDTE
Between
> 20060304 And 20060331) AND (ITH.TTYPE In ('RP','RS','N')) AND
> (ITH.THCURR<>'INR'))
> GROUP BY ITH.TCOM, ITH.TTYPE, ITH.TWHS, ITH.THRNO, IIM.IITYP, ITH.TTDTE,
> IIM.IPURC, ITH.TREF, AVM.VENDOR, AVM.VNDNAM, ITH.THCURR, HPOX.POGSPR,
> ITH.TPROD, IIM.IDESC, ITH.TQTY, ITH.THLIN, IIMX.IXLOC1, ITH.TLOCT,
AVM.VTERMS
> ORDER BY ITH.TTDTE)
>
> i need to apply distinct for ITH.TCOM field from ITH file.
>
>
> "Tim Williams" wrote:
>
> > What database are you using and do you have a query which works when run
in
> > another query tool?
> > How many column/what types etc...?
> >
> > Tim
> >
> > "Prakash" <Prakash@discussions.microsoft.com> wrote in message
> > news:5DD3F79F-A3C5-461C-B379-6EF4B221EC54@microsoft.com...
> > >I tried, but no result. It could not filter duplicate entries. i need
to
> > > filter only 1st row of the duplicate entry.
> > >
> > > "Tim Williams" wrote:
> > >
> > >> Have you tried
> > >>
> > >> select field1,field2,.....
> > >> from tablename
> > >> group by field1, field2,....
> > >>
> > >> Tim
> > >>
> > >>
> > >> "Prakash" <Prakash@discussions.microsoft.com> wrote in message
> > >> news:6B564B61-B19F-403F-BAC2-D45794FE44A0@microsoft.com...
> > >> > Hi,
> > >> > I wanted to use Distinct Command thro MS query (excel)for filtering
> > >> > duplication from other database. PLs confirm how i can use & syntax
for
> > >> > the
> > >> > same.
> > >> >
> > >> > I have tried "SELECT DISTINCT FIELD1,FIELD2,..... FROM TABLE"
> > >> >
> > >> > Regards
> > >>
> > >>
> > >>
> >
> >
> >