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

USE OF DISTINCT COMMAND IN MS QUERY
 

Have you tried

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

Tim


"Prakash" wrote in message
...
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

USE OF DISTINCT COMMAND IN MS QUERY
 

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" wrote in message
...
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

USE OF DISTINCT COMMAND IN MS QUERY
 

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" wrote in message
...
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" wrote in message
...
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

USE OF DISTINCT COMMAND IN MS QUERY
 

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" wrote in message
...
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" wrote in message
...
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

USE OF DISTINCT COMMAND IN MS QUERY
 

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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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