[Cialug] OT: SQL Help?

[Kenneth Younger]

I'll admit my response was based on experience, but perhaps not a full
experience. I know this doesn't work in Access, SQL Server, and Oracle. But
I wasn't sure about other DBs. :)

Here's Access: http://imgur.com/02fhIxG
And here's a SQL Fiddle with our example, this will let you test several
other DBs: http://sqlfiddle.com/#!1/00e9d/1

MySQL doesn't require the aggregation function (which I think should be
forced as explicit). SQLite is the only other DB on Fiddle that this works
with implicitly.

If he's working in the MS world, then I'm guessing being implicit might not
work.

-Kenny


On Wed, Oct 2, 2013 at 2:30 PM, jim kraai <jimgkraai at gmail.com> wrote:

> whups, forgot to put a smile at the end of that
>
> i'm very rarely a complete ass
>
>
> On Wed, Oct 2, 2013 at 2:29 PM, jim kraai <jimgkraai at gmail.com> wrote:
>
> > Kenneth, please give a counter-example of how my solution does not meet
> > the stated requirements with the stated data.
> >
> > Otherwise you're ruining my implicit lesson on effectively stating
> > requirements.
> >
> > --jim
> >
> >
> >
> >
> > On Wed, Oct 2, 2013 at 2:08 PM, Kenneth Younger <kyounger at gmail.com
> >wrote:
> >
> >> Jim's suggestion won't quite work, as you'll need some aggregation
> >> function
> >> for both fname and lname if you group on only streetaddress.
> >>
> >> Take a look at the OVER clause in various SQLs, or some have a COALESCE
> >> function that you can comma delimit a field (or group of fields).
> >>
> >> -Kenny
> >>
> >>
> >> On Wed, Oct 2, 2013 at 1:58 PM, jim kraai <jimgkraai at gmail.com> wrote:
> >>
> >> > select Fname, Lname, StreetAddress
> >> > from SampleTable
> >> > group by StreetAddress;
> >> >
> >> >
> >> >
> >> > On Wed, Oct 2, 2013 at 1:45 PM, 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.
> >> > >
> >> > > My plan was to do an outer join - using subqueries- one subquery
> with
> >> > >
> >> > > select distinct streetaddress from SampleTable
> >> > > and the other with
> >> > > select * from SampleTable
> >> > >
> >> > > With the Join on StreetAddress
> >> > >
> >> > > My understanding was if I used an outer join, using my table (in
> this
> >> > > example) only 3 address records as the required table and the whole
> >> > table I
> >> > > would only get 3 items in my table.  The query gives results, but I
> >> get
> >> > the
> >> > > whole table again (all 5 records) and it does not seem to be working
> >> > like I
> >> > > expect.
> >> > >
> >> > > Does anyone else have any thoughts on how I might attack this
> problem?
> >> > >  Keep in mind there are a lot of limitations using JET and msquery.
> >>  The
> >> > > table does have an ID field to make each row unique.
> >> > >
> >> > _______________________________________________
> >> > Cialug mailing list
> >> > Cialug at cialug.org
> >> > http://cialug.org/mailman/listinfo/cialug
> >> >
> >> > --
> >> > Kenneth Younger III
> >> > Founder, Sheer Focus Inc.
> >> > e: kenny at sheerfocus.com
> >> > p: (515) 367-0001
> >> > t: @kenny <http://twitter.com/kenny>
> >> > <http://cialug.org/mailman/listinfo/cialug>
> >> >
> >> _______________________________________________
> >> Cialug mailing list
> >> Cialug at cialug.org
> >> http://cialug.org/mailman/listinfo/cialug
> >>
> >
> >
> _______________________________________________
> Cialug mailing list
> Cialug at cialug.org
> http://cialug.org/mailman/listinfo/cialug
>
> --
> Kenneth Younger III
> Founder, Sheer Focus Inc.
> e: kenny at sheerfocus.com
> p: (515) 367-0001
> t: @kenny <http://twitter.com/kenny>
> <http://cialug.org/mailman/listinfo/cialug>
>