[SOLVED] Distinct in loop issue

[DavidMR]

ok guys, this is driving me mental, im about to lose my mind  ???

 

here is the problem, i have 58 million lines in the database, with over 30 different columns.

 

Now, if a certain lines, matches others with 3 criteria, i only want that line to appear once... example :

 

before:

--------

acrobat  233    c:\acrobat    FULL

acrobat  322    c:\acrobat    Partial

acrobat  233    c:\acrobat    FULL  <-- would be ignored as it already matched 3 unique criteria

acrobit    333    c:\acrobit    FULL

 

after:

--------

acrobat  233    c:\acrobat    FULL

acrobat  322    c:\acrobat    Partial

acrobit  333    c:\acrobit    FULL

 

Now pay no attention to the fact that there is only 4 columns, as i wish to return all 30, but only entries that are unique with the name,size and directory

 

this will cut my 58 million lines, down to 2 million lines so as you can see, its a small change

 

pesucode may also help explain this(do not mention the word concat)

 

select * from files where (distinct name,size,directory)

 

this is an illegal statement but hopefully you can see what i need

 

Any ideas?

[Vikas Jayna]

Well! what if there are two rows having the same value for the 4 columns but different values for the rest. Which one of these rows would you want in the output of the query?

[DavidMR]

good point, in this case it dosent matter because we only need to verify the first 3, the rest of the fields are secondary, i think i have solved this though, would this be a safe way to go? :

 

SELECT [name],max(),max([directory]),max([publisher]),max([asset tag])

FROM files

GROUP BY [name],

ORDER BY [name]

 

this would work wouldnt it?

[Vikas Jayna]

Ideally the query should be:

 

SELECT [name],,max([directory]),max([publisher]),max([asset tag])

FROM files

GROUP BY [name],

ORDER BY [name]

[DavidMR]

Ideally the query should be:

 

SELECT [name],,max([directory]),max([publisher]),max([asset tag])

FROM files

GROUP BY [name],

ORDER BY [name]

 

lol thats what i meant, i was just free-coding it, i would have applied it too all the fields

 

thanks anyways