using WITH query AS, cte() with postgresql

[Nathan Mailg]

I'm using SA 0.8.2 and trying to port this query that works with PostgreSQL 9.2.4:

WITH distinct_query AS (
SELECT DISTINCT ON (refid) *
FROM appl
WHERE lastname ILIKE 'Williamson%' AND firstname ILIKE 'd%'
GROUP BY refid, id, lastname, firstname, appldate
ORDER BY refid, appldate DESC
)
SELECT * FROM distinct_query ORDER BY lastname, firstname;

I've worked on this quite a while and I'm stuck. I've tried every construct in the docs that looks like it might work without success (subquery, join, select, etc). Here's what I've been referencing most recently that I think is the closest to what I want:

http://docs.sqlalchemy.org/en/rel_0_8/orm/query.html?highlight=cte#sqlalchemy.orm.query.Query.cte

Here's what I have so far:

qlast, qfirst = params['query'].split(' ', 1)
subq = DBSession.query(Appl).\
distinct(Appl.refid).\
filter(getattr(Appl, 'lastname').match(qlast)).\
filter(getattr(Appl, 'firstname').ilike(qfirst+'%')).\
group_by(Appl.refid).\
group_by(Appl.appldate).\
order_by(Appl.refid).\
order_by(Appl.appldate.desc()).\
cte('distinct_query')
a = aliased(subq, name='distinct_query_alias')
rows = DBSession.query(Appl).\
order_by(a.c.lastname.asc()).\
order_by(a.c.firstname.asc()).\
order_by(a.c.middlename.asc())

Below is the error output. Sorry for the length of output; I elided as much as possible, but didn't want to remove anything that might be important:

(ProgrammingError) missing FROM-clause entry for table "distinct_query_alias"
LINE 2: ...race_2 ON race_2.id = applbenef_1.raceid ORDER BY distinct_q...
^
'SELECT appl.id AS appl_id, appl.refid AS appl_refid,
appl.appldate AS appl_appldate, appl.lastname AS appl_lastname,
appl.firstname AS appl_firstname, appl.middlename AS appl_middlename,
appl.cityid AS appl_cityid, appl.cityid2 AS appl_cityid2,
appl.raceid AS appl_raceid, appl.maritalid AS appl_maritalid,
appl.referral AS appl_referral,
city_1.id AS city_1_id, city_1.name AS city_1_name,
[... city_1.xxx AS city_1_xxx ...]
city_2.id AS city_2_id, city_2.name AS city_2_name,
[... city_2.xxx AS city_2_xxx ...],
race_1.id AS race_1_id, race_1.name AS race_1_name,
race_1.race AS race_1_race, race_1.rowcreated AS race_1_rowcreated,
race_1.rowmodified AS race_1_rowmodified,
marital_1.id AS marital_1_id, marital_1.name AS marital_1_name,
marital_1.marital AS marital_1_marital,
marital_1.rowcreated AS marital_1_rowcreated,
marital_1.rowmodified AS marital_1_rowmodified,
agency_1.id AS agency_1_id, agency_1.name AS agency_1_name,
[... agency_1.xxx AS agency_1_xxx ...],
applreferrer_1.id AS applreferrer_1_id,
applreferrer_1.applid AS applreferrer_1_applid,
applreferrer_1.agencyid AS applreferrer_1_agencyid,
[... applreferrer_1.xxx AS applreferrer_1_xxx ...],
applsponsor_1.id AS applsponsor_1_id,
applsponsor_1.applid AS applsponsor_1_applid,
[... applsponsor_1.xxx AS applsponsor_1_xxx ...],
race_2.id AS race_2_id, race_2.name AS race_2_name,
race_2.race AS race_2_race, race_2.rowcreated AS race_2_rowcreated,
race_2.rowmodified AS race_2_rowmodified,
applbenef_1.id AS applbenef_1_id,
applbenef_1.applid AS applbenef_1_applid,
applbenef_1.lastname AS applbenef_1_lastname,
applbenef_1.firstname AS applbenef_1_firstname,
applbenef_1.age AS applbenef_1_age,
applbenef_1.raceid AS applbenef_1_raceid,
applbenef_1.notes AS applbenef_1_notes,
applbenef_1.rowcreated AS applbenef_1_rowcreated,
applbenef_1.rowmodified AS applbenef_1_rowmodified \n
FROM appl
LEFT OUTER JOIN city AS city_1 ON city_1.id = appl.cityid
LEFT OUTER JOIN city AS city_2 ON city_2.id = appl.cityid2
LEFT OUTER JOIN race AS race_1 ON race_1.id = appl.raceid
LEFT OUTER JOIN marital AS marital_1 ON marital_1.id = appl.maritalid
LEFT OUTER JOIN applreferrer AS applreferrer_1 ON appl.id = applreferrer_1.applid
LEFT OUTER JOIN agency AS agency_1 ON agency_1.id = applreferrer_1.agencyid
LEFT OUTER JOIN applsponsor AS applsponsor_1 ON appl.id = applsponsor_1.applid
LEFT OUTER JOIN applbenef AS applbenef_1 ON appl.id = applbenef_1.applid
LEFT OUTER JOIN race AS race_2 ON race_2.id = applbenef_1.raceid
ORDER BY
distinct_query_alias.lastname ASC,
distinct_query_alias.firstname ASC,
distinct_query_alias.middlename ASC,
applreferrer_1.id,
applsponsor_1.id,
applbenef_1.id' {}

class Appl(Base):
__tablename__ = 'appl'
id = Column(Integer, primary_key=True)
refid = Column(Integer, Sequence('appl_refid_seq'))
appldate = Column(Date)
lastname = Column(Unicode(50))
firstname = Column(Unicode(50))
middlename = Column(Unicode(50))
cityid = Column(Integer, ForeignKey('city.id'))
cityid2 = Column(Integer, ForeignKey('city.id'))
raceid = Column(Integer, ForeignKey('race.id'))
maritalid = Column(Integer, ForeignKey('marital.id'))
#
city = relationship('City', lazy='joined', primaryjoin='City.id==Appl.cityid')
city2 = relationship('City', lazy='joined', primaryjoin='City.id==Appl.cityid2')
race = relationship('Race', lazy='joined')
marital = relationship('Marital', lazy='joined')
applrefs = relationship('ApplReferrer', cascade="all, delete, delete-orphan",
lazy='joined', order_by='ApplReferrer.id')
applsponsors = relationship('ApplSponsor', backref='appl', cascade="all, delete, delete-orphan",
lazy='joined', order_by='ApplSponsor.id')
applbenefs = relationship('ApplBenef', cascade="all, delete, delete-orphan",
lazy='joined', order_by='ApplBenef.id')

Please let me know if you need more info. Thanks!

[Michael Bayer]

here's a proof of concept which completes in postgresql for me:

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Appl(Base):
__tablename__ = 'appl'

id = Column(Integer, primary_key=True)

firstname = Column(String)
lastname = Column(String)
refid = Column(Integer)
appldate = Column(DateTime)

e = create_engine("postgresql://scott:tiger@localhost/test", echo=True)
Base.metadata.create_all(e)

s = Session(e)

q1 = s.query(distinct(Appl.refid), Appl).\
filter(Appl.lastname.ilike('Williamson%')).\
filter(Appl.firstname.ilike('d%')).\
group_by(Appl).\
order_by(Appl.refid, Appl.appldate.desc())

q1 = q1.cte('distinct_query')
q2 = s.query(q1).order_by(q1.c.lastname, q1.c.firstname)


q2.all()

query outputs as:

WITH distinct_query AS
(SELECT DISTINCT appl.refid AS anon_1, appl.id AS id, appl.firstname AS firstname, appl.lastname AS lastname, appl.refid AS refid, appl.appldate AS appldate
FROM appl
WHERE appl.lastname ILIKE %(lastname_1)s AND appl.firstname ILIKE %(firstname_1)s GROUP BY appl.id, appl.firstname, appl.lastname, appl.refid, appl.appldate ORDER BY appl.refid, appl.appldate DESC)
SELECT distinct_query.anon_1 AS distinct_query_anon_1, distinct_query.id AS distinct_query_id, distinct_query.firstname AS distinct_query_firstname, distinct_query.lastname AS distinct_query_lastname, distinct_query.refid AS distinct_query_refid, distinct_query.appldate AS distinct_query_appldate
FROM distinct_query ORDER BY distinct_query.lastname, distinct_query.firstname


turning it into aliased(), while unnecessary, works also:

q1 = q1.cte('distinct_query')
q1 = aliased(q1, 'd_q_a')
q2 = s.query(q1).order_by(q1.c.lastname, q1.c.firstname)
q2.all()

> --
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/groups/opt_out.

[Nathan Mailg]

Thanks Mike for all the help! I think we're really close.

Unfortunately, the "DISTINCT ON (refid) *" syntax appears to be needed, as opposed to "DISTINCT (refid), *", since the following query in psql is not filtering the duplicate refid's:

WITH distinct_query AS (SELECT DISTINCT (refid), *

FROM appl
WHERE lastname ILIKE 'Williamson%' AND firstname ILIKE 'd%'
GROUP BY refid, id, lastname, firstname, appldate
ORDER BY refid, appldate DESC
)

SELECT * from distinct_query ORDER BY lastname, firstname;

and the above is what appears to be the equivalent of:

> q1 = s.query(distinct(Appl.refid), Appl).\
> filter(Appl.lastname.ilike('Williamson%')).\
> filter(Appl.firstname.ilike('d%')).\
> group_by(Appl).\
> order_by(Appl.refid, Appl.appldate.desc())
> q1 = q1.cte('distinct_query')
> q2 = s.query(q1).order_by(q1.c.lastname, q1.c.firstname)

I got the above code running with no exceptions, so that's great, but it's not getting rid of rows with the same refid in "distinct_query". Is there a way I can get "DISTINCT ON (refid) *" generated?

Sorry to tag on a followup, but how would I get the following relationship mapped onto the 2nd, non-distinct SELECT ("q2" above)? From the "Appl" class below:

>> city = relationship('City', lazy='joined', primaryjoin='City.id==Appl.cityid')

I tried some aliased join variations but couldn't get anything to work. I think there's some additional configuration needed as I have two columns (cityid and cityid2) in table Appl that reference the City table.

I really appreciate all your help!

On Sep 17, 2013, at 10:51 PM, Michael Bayer <mik...@zzzcomputing.com> wrote:

> here's a proof of concept which completes in postgresql for me:
>

> […]

>> […]

[Michael Bayer]

OK, like this then:

q1 = s.query(Appl).distinct(Appl.refid).\

filter(Appl.lastname.ilike('Williamson%')).\
filter(Appl.firstname.ilike('d%')).\
group_by(Appl).\
order_by(Appl.refid, Appl.appldate.desc())

output:

WITH distinct_query AS
(SELECT DISTINCT ON (appl.refid) appl.id AS id, appl.firstname AS firstname, appl.lastname AS lastname, appl.refid AS refid, appl.appldate AS appldate

FROM appl
WHERE appl.lastname ILIKE %(lastname_1)s AND appl.firstname ILIKE %(firstname_1)s GROUP BY appl.id, appl.firstname, appl.lastname, appl.refid, appl.appldate ORDER BY appl.refid, appl.appldate DESC)

SELECT distinct_query.id AS distinct_query_id, distinct_query.firstname AS distinct_query_firstname, distinct_query.lastname AS distinct_query_lastname, distinct_query.refid AS distinct_query_refid, distinct_query.appldate AS distinct_query_appldate

FROM distinct_query ORDER BY distinct_query.lastname, distinct_query.firstname

I know this is not exactly the same, but the query itself is a SELECT DISTINCT ON(x), which seems to be what they're getting at when I look at http://www.postgresql.org/docs/9.0/static/sql-select.html#SQL-DISTINCT.

[Nathan Mailg]

That's it, awesome! Thank you!

>>> q2 = s.query(q1).order_by(q1.c.lastname, q1.c.firstname)

>> Sorry to tag on a followup, but how would I get the following relationship mapped onto the 2nd, non-distinct SELECT ("q2" above)? From the "Appl" class below:
>>
>>>> city = relationship('City', lazy='joined', primaryjoin='City.id==Appl.cityid')
>>
>> I tried some aliased join variations but couldn't get anything to work. I think there's some additional configuration needed as I have two columns (cityid and cityid2) in table Appl that reference the City table.

Thanks again for all your help!

[Nathan Mailg]

Using q1:

q1 = s.query(Appl).\

distinct(Appl.refid).\
filter(Appl.lastname.ilike('Williamson%')).\
filter(Appl.firstname.ilike('d%')).\
group_by(Appl).\
order_by(Appl.refid, Appl.appldate.desc())

this q2 works as a simple join:

q2 = s.query(q1).\
join(City, City.id==q1.c.cityid).\
order_by(q1.c.lastname, q1.c.firstname)

but there's no relationship mapped to the returned row objects like my "Appl" class has:

city = relationship('City', lazy='joined', primaryjoin='City.id==Appl.cityid')

I've tried:

c = aliased(City)
q2 = s.query(q1).\
join(Appl.city).\
join(c, Appl.city).\
order_by(q1.c.lastname, q1.c.firstname)

but that returns:

[…]
File "/Users/nathan/projects/env/lib/python2.7/site-packages/SQLAlchemy-0.8.2-py2.7-macosx-10.8-x86_64.egg/sqlalchemy/orm/mapper.py", line 1765, in common_parent
return self.base_mapper is other.base_mapper
AttributeError: 'CTE' object has no attribute 'base_mapper'

I need to somehow combine the above 2 "q2" forms to specify both "join(City, City.id==q1.c.cityid)" and "Appl.city" in join()?

Thanks!

[Michael Bayer]

On Sep 19, 2013, at 2:46 PM, Nathan Mailg <natha...@gmail.com> wrote:

>
> c = aliased(City)
> q2 = s.query(q1).\
> join(Appl.city).\
> join(c, Appl.city).\
> order_by(q1.c.lastname, q1.c.firstname)

why don't you join on the Column objects present rather than relying on the relationship?

[Nathan Mailg]

Sorry, I don't know. I'm sure I'm missing something basic that's obvious to you. :)

Do you mean something like this?

q2 = s.query(q1).\
join(City, City.id==q1.c.cityid).\
order_by(q1.c.lastname, q1.c.firstname)

That's all I've been able to get to work. I think I've been spoiled by relationship() using declarative. :)

What I'm trying to get at are the attrs on City, like:

# row is KeyedTuple instance
for row in q2.all():
# this works
print row.lastname
# below does not work,
# stuck here trying to get at joined City attrs, e.g. City has a "name" attr
print row.city.name

Sorry for my confusion.

[Michael Bayer]

On Sep 20, 2013, at 12:33 AM, Nathan Mailg <natha...@gmail.com> wrote:

> On Sep 19, 2013, at 3:07 PM, Michael Bayer <mik...@zzzcomputing.com> wrote:
>>
>> On Sep 19, 2013, at 2:46 PM, Nathan Mailg <natha...@gmail.com> wrote:
>>
>>> c = aliased(City)
>>> q2 = s.query(q1).\
>>> join(Appl.city).\
>>> join(c, Appl.city).\
>>> order_by(q1.c.lastname, q1.c.firstname)
>>
>> why don't you join on the Column objects present rather than relying on the relationship?
>
> Sorry, I don't know. I'm sure I'm missing something basic that's obvious to you. :)
>
> Do you mean something like this?
>
> q2 = s.query(q1).\
> join(City, City.id==q1.c.cityid).\
> order_by(q1.c.lastname, q1.c.firstname)
>
> That's all I've been able to get to work. I think I've been spoiled by relationship() using declarative. :)

that's what I meant, yup. So you *can* do the query, you're just missing a particular convenience feature in this case.

> What I'm trying to get at are the attrs on City, like:
>
> # row is KeyedTuple instance
> for row in q2.all():
> # this works
> print row.lastname
> # below does not work,
> # stuck here trying to get at joined City attrs, e.g. City has a "name" attr
> print row.city.name
>

Ok now I'm still not following - in this case, "row" is a NamedTuple, or a mapped instance? if its a NamedTuple then you don't have the service of traversing along object relationships available since the NamedTuple isn't a mapped object. You'd need to query for a full object with an identity ( query(MyClass) as opposed to query(MyClass.col1, MyClass.col2, ...)

if you provide me with *very minimal, but working* mappings and the query we're working on, I can show you how to make it load entities rather than rows.

[Nathan Mailg]

On Sep 20, 2013, at 11:59 AM, Michael Bayer <mik...@zzzcomputing.com> wrote:

>
> Ok now I'm still not following - in this case, "row" is a NamedTuple, or a mapped instance? if its a NamedTuple then you don't have the service of traversing along object relationships available since the NamedTuple isn't a mapped object. You'd need to query for a full object with an identity ( query(MyClass) as opposed to query(MyClass.col1, MyClass.col2, ...)
>
> if you provide me with *very minimal, but working* mappings and the query we're working on, I can show you how to make it load entities rather than rows.
>

In the debugger the returned row was of type "KeyedTuple", which I think is derived from "NamedTuple", so yes, it's a NamedTuple and not a mapped instance.

I really appreciate your help with this. Below is as stripped down as I can make it while still showing the moving parts:

class Appl(Base):
__tablename__ = 'appl'
id = Column(Integer, primary_key=True)
refid = Column(Integer, Sequence('appl_refid_seq'))
appldate = Column(Date)
lastname = Column(Unicode(50))
firstname = Column(Unicode(50))

cityid = Column(Integer, ForeignKey('city.id'))
cityid2 = Column(Integer, ForeignKey('city.id'))

#

city = relationship('City', lazy='joined', primaryjoin='City.id==Appl.cityid')

city2 = relationship('City', lazy='joined', primaryjoin='City.id==Appl.cityid2')

class City(Base):
__tablename__ = 'city'
id = Column(Integer, primary_key=True)
name = Column(Unicode(30), nullable=False)
state = Column(Unicode(2), nullable=False)
zipcode = Column(Unicode(10))

qlast, qfirst = params['query'].split(' ', 1)

d = DBSession.query(Appl).\
distinct(Appl.refid).\
filter(Appl.lastname.ilike(qlast)).\
filter(Appl.firstname.ilike(qfirst+'%')).\
group_by(Appl).\
order_by(Appl.refid, Appl.appldate.desc())
d = d.cte('distinct_query')
q = DBSession.query(d).\
join(City, City.id==d.c.cityid).\
order_by(d.c.lastname, d.c.firstname)

What I'm trying to get at are the attrs on City after running "q", like:

# row is KeyedTuple instance

for row in q.all():

# this works
print row.lastname
# below does not work,
# stuck here trying to get at joined City attrs, e.g. City has a "name" attr
print row.city.name

Thank you!

[Michael Bayer]

here is a demo:

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Appl(Base):
__tablename__ = 'appl'
id = Column(Integer, primary_key=True)

refid = Column(Integer)

lastname = Column(Unicode(50))
firstname = Column(Unicode(50))
cityid = Column(Integer, ForeignKey('city.id'))

city = relationship('City')

class City(Base):
__tablename__ = 'city'
id = Column(Integer, primary_key=True)
name = Column(Unicode(30), nullable=False)

e = create_engine("postgresql://scott:tiger@localhost/test", echo=True)

Base.metadata.drop_all(e)
Base.metadata.create_all(e)

DBSession = Session(e)

c1, c2, c3 = City(name='c1'), City(name='c2'), City(name='c3')
DBSession.add_all([
Appl(firstname='b', lastname='a', refid=1, city=c1),
Appl(firstname='b', lastname='a', refid=1, city=c2),
Appl(firstname='b', lastname='a', refid=2, city=c3),
Appl(firstname='q', lastname='z', refid=2, city=c1),
Appl(firstname='b', lastname='a', refid=3, city=c2),
])

qlast, qfirst = 'a', 'b'
d = DBSession.query(Appl).\
distinct(Appl.refid).\
filter(Appl.lastname == qlast).\
filter(Appl.firstname == qfirst).\
group_by(Appl).\
order_by(Appl.refid)

d = d.cte('distinct_query')

q = DBSession.query(Appl).select_entity_from(d).\
join(Appl.city).\
order_by(d.c.lastname, d.c.firstname)

for row in q:
print row.refid, row.firstname, row.lastname, row.city.name

[Nathan Mailg]

Thanks Mike, that works!

I really appreciate you taking the time to show me how to get this working.

Do you have an amazon wish list or something like that somewhere?

[Nathan Mailg]

Sorry for this follow-up, but I'm really at a loss as to what to check next…

What should I do if after I insert a new "Appl" row, the query we've worked on in this thread is *not* finding it, i.e. it's not being returned in the result set? I'm really surprised and thinking I must be missing something really basic?

I've deleted the lastname, firstname indexes and re-created them, but the newly created row is still *not* being returned!

Here's the sql insert from the SA log:

2013-10-03 12:26:37,638 INFO [sqlalchemy.engine.base.Engine][Dummy-3] BEGIN (implicit)
2013-10-03 12:26:37,643 INFO [sqlalchemy.engine.base.Engine][Dummy-3] INSERT INTO appl (refid, appldate, lastname, firstname, cityid, cityid2) VALUES (nextval('appl_refid_seq'), %(appldate)s, %(lastname)s, %(firstname)s, %(cityid)s, %(cityid2)s) RETURNING appl.id
2013-10-03 12:26:37,643 INFO [sqlalchemy.engine.base.Engine][Dummy-3] {'cityid2': None, 'cityid': None, 'firstname': u'test3', 'lastname': u'test3', 'appldate': u'10/3/2013'}
2013-10-03 12:26:37,644 DEBUG [sqlalchemy.engine.base.Engine][Dummy-3] Col ('id',)
2013-10-03 12:26:37,644 DEBUG [sqlalchemy.engine.base.Engine][Dummy-3] Row (14574,)
2013-10-03 12:26:37,645 INFO [sqlalchemy.engine.base.Engine][Dummy-3] COMMIT

What's also really odd, is if I manually run the sql in psql, it works and returns the new row:


WITH distinct_query AS (
SELECT DISTINCT ON (refid) id, refid, lastname, firstname, appldate
FROM appl WHERE lastname ILIKE 'test%'

GROUP BY refid, id, lastname, firstname, appldate ORDER BY refid, appldate DESC
)
SELECT * from distinct_query ORDER BY lastname, firstname;

Another odd thing is in my application, I have another search feature, and it's using an SA query that's not using the cte/distinct query below, and it works!

Sorry to have to ask this, but I'm stuck and need to get this working.

Thanks again for all the help!

[Michael Bayer]

are you using serializable isolation ? that would prevent an in-progress transaction from seeing this new row committed elsewhere.

other than that, the SELECT in the log will show you what rows it's selecting and using echo='debug' will show the rows it finds on the way back.

[Nathan Mailg]

I don't know why, but removing the "join(Appl.city)" call in "q" fixed it.

Below is the SA output of just the join section, as the full, generated query output is very long:

Not working, returning only 1 row of 4 expected rows:

q = DBSession.query(Appl).\
select_entity_from(d).\
join(Appl.city).\
order_by(d.c.lastname, d.c.firstname)

FROM distinct_query JOIN city ON city.id = distinct_query.cityid LEFT OUTER JOIN city AS city_1 ON city_1.id = distinct_query.cityid LEFT OUTER JOIN city AS city_2 ON city_2.id = distinct_query.cityid2 LEFT OUTER JOIN race AS race_1 ON race_1.id = distinct_query.raceid LEFT OUTER JOIN marital AS marital_1 ON marital_1.id = distinct_query.maritalid LEFT OUTER JOIN applreferrer AS applreferrer_1 ON distinct_query.id = applreferrer_1.applid LEFT OUTER JOIN agency AS agency_1 ON agency_1.id = applreferrer_1.agencyid LEFT OUTER JOIN applsponsor AS applsponsor_1 ON distinct_query.id = applsponsor_1.applid LEFT OUTER JOIN applbenef AS applbenef_1 ON distinct_query.id = applbenef_1.applid LEFT OUTER JOIN race AS race_2 ON race_2.id = applbenef_1.raceid ORDER BY distinct_query.lastname, distinct_query.firstname, distinct_query.middlename, distinct_query.maidenname, applreferrer_1.id, applsponsor_1.id, applbenef_1.id

Working, returning 4 of 4 expected rows:

q = DBSession.query(Appl).\
select_entity_from(d).\
order_by(d.c.lastname, d.c.firstname)

FROM distinct_query LEFT OUTER JOIN city AS city_1 ON city_1.id = distinct_query.cityid LEFT OUTER JOIN city AS city_2 ON city_2.id = distinct_query.cityid2 LEFT OUTER JOIN race AS race_1 ON race_1.id = distinct_query.raceid LEFT OUTER JOIN marital AS marital_1 ON marital_1.id = distinct_query.maritalid LEFT OUTER JOIN applreferrer AS applreferrer_1 ON distinct_query.id = applreferrer_1.applid LEFT OUTER JOIN agency AS agency_1 ON agency_1.id = applreferrer_1.agencyid LEFT OUTER JOIN applsponsor AS applsponsor_1 ON distinct_query.id = applsponsor_1.applid LEFT OUTER JOIN applbenef AS applbenef_1 ON distinct_query.id = applbenef_1.applid LEFT OUTER JOIN race AS race_2 ON race_2.id = applbenef_1.raceid ORDER BY distinct_query.lastname, distinct_query.firstname, distinct_query.middlename, distinct_query.maidenname, applreferrer_1.id, applsponsor_1.id, applbenef_1.id

Thanks Mike!