USE OF DISTINCT COMMAND IN MS QUERY

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Prakash - 18 Mar 2006 11:01 GMT

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

Reply to this Message

Tim Williams - 19 Mar 2006 05:43 GMT

Have you tried

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

Tim

> Hi,
> I wanted to use Distinct Command thro MS query (excel)for filtering
[quoted text clipped - 5 lines]
>
> Regards

Reply to this Message

Prakash - 20 Mar 2006 03:49 GMT

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

> Have you tried
>
[quoted text clipped - 13 lines]
> >
> > Regards

Reply to this Message

Tim Williams - 20 Mar 2006 08:10 GMT

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

>I tried, but no result. It could not filter duplicate entries. i need to
> filter only 1st row of the duplicate entry.
[quoted text clipped - 16 lines]
>> >
>> > Regards

Reply to this Message

Prakash - 24 Mar 2006 09:36 GMT

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.

> What database are you using and do you have a query which works when run in
> another query tool?
[quoted text clipped - 22 lines]
> >> >
> >> > Regards

Reply to this Message

Tim Williams - 25 Mar 2006 00:03 GMT

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.

Signature

Tim Williams
Palo Alto, CA

> Thanks for you suggetion.
> I am using AS400 database files, one file contains around 1500 rows & 15
[quoted text clipped - 44 lines]
> > >> >
> > >> > Regards

Reply to this Message