[Cialug] OT: SQL Help?

[Zachary Kotlarek]

On Oct 2, 2013, at 11:45 AM, "Nathan C. Smith" <NSmith at hhlawpc.com> wrote:

> 
> Hello SQL Gurus.
> 
> I am trying to do a quick and dirty operation on a mailing list somebody provided to me in Excel.  What I want to do is reduce the list so that only one is sent to each household to save paper and postage.  The list is something like this:
> SampleTable
> Fname   Lname   StreetAddress
> Joe     Public          801 Towanda Drive
> Mary    Public  801 Towanda Drive
> Joe Jr. Public  801 Towanda Drive
> Peter   Brady   1401 Decker
> Mr.     Rogers  1024 bitwise Ave.
> You get the idea.
> 
> I was just trying to use the tools at hand - MS Excel and msquery.  So I set the Excel Table up as a datasource in ODBC.



If Excel supports aggregating strings you probably want to "GROUP BY StreetAddress" rather than using DISTINCT. Something like:
	SELECT MIN(FName), MIN(LName), StreetAddress FROM SampleTable GROUP BY StreetAddress
it may also work without the aggregation functions, and just randomly pick one of the results with:
	SELECT FName, LName, StreetAddress FROM SampleTable GROUP BY StreetAddress
which isn't great if you need reproducibility but has been known to work in some SQL engines and would solve your problem.

You can VBA this one in just a few lines -- sort by street address and then just walk the table from the top, skipping rows where thisRow.addr == lastRow.addr and copying the rest to a new table (or marking the dups, or uniques, or whatever else makes it easiest to get your data out).

	Zach

-------------- next part --------------
A non-text attachment was scrubbed...
Name: smime.p7s
Type: application/pkcs7-signature
Size: 2749 bytes
Desc: not available
URL: <http://cialug.org/pipermail/cialug/attachments/20131002/5be559f3/attachment-0001.bin>