php - Does ORDER BY apply before or after DISTINCT? -

-

in mysql query, when using distinct option, order by apply after duplicates removed? if not, there way make so? think it's causing issues code.

edit:
here's more information what's causing problem. understand that, @ first glance, order not important, since dealing duplicate rows. however, not entirely case, since using inner join sort rows.

say have table of forum threads, containing data:

+----+--------+-------------+ | id | userid |    title    | +----+--------+-------------+ |  1 |      1 | information | |  2 |      1 | faq         | |  3 |      2 | support     | +----+--------+-------------+

i have set of posts in table this:

+----+----------+--------+---------+ | id | threadid | userid | content | +----+----------+--------+---------+ |  1 |        1 |      1 | lorem   | |  2 |        1 |      2 | ipsum   | |  3 |        2 |      2 | test    | |  4 |        3 |      1 | foo     | |  5 |        2 |      3 | bar     | |  6 |        3 |      5 | bob     | |  7 |        1 |      2 | joe     | +----+----------+--------+---------+

i using following mysql query threads, sort them based on latest post (assuming posts higher ids more recent:

select t.* threads t inner join posts p on t.id = p.threadid order p.id desc

this works, , generates this:

+----+--------+-------------+ | id | userid |    title    | +----+--------+-------------+ |  1 |      1 | information | |  3 |      2 | support     | |  2 |      1 | faq         | |  3 |      2 | support     | |  2 |      1 | faq         | |  1 |      1 | information | |  1 |      1 | information | +----+--------+-------------+

however, can see, information correct, there duplicate rows. i'd remove such duplicates, used select distinct instead. however, yielded following:

+----+--------+-------------+ | id | userid |    title    | +----+--------+-------------+ |  3 |      2 | support     | |  2 |      1 | faq         | |  1 |      1 | information | +----+--------+-------------+

this wrong, since "information" thread should on top. seem using distinct causes duplicates removed top bottom, final rows left. causes issues in sorting.

is case, or analyzing things incorrectly?

two things understand:

  1. generally speaking, resultsets unordered unless specify order by clause; extent specify non-strict order (i.e. order by on non-unique columns), order in records equal under ordering appear within resultset undefined.

    i suspect may specifying such non-strict order, root of problems: ensure ordering strict specifying order by on set of columns sufficient uniquely identify each record care final position in resultset.

  2. distinct may use group by, causes results ordered grouped columns; is, select distinct a, b, c t produce resultset appears though order a, b, c has been applied. again, specifying sufficiently strict order meet needs override effect.

following update, bearing in mind point #2 above, clear effect of grouping results achieve distinct makes impossible order non-grouped column p.id; instead, want:

select   t.*     threads t inner join posts p on t.id = p.threadid group t.id order max(p.id) desc

Comments

Post a Comment

Popular posts from this blog

django - How can I change user group without delete record -

-
on website user have 1 group. , user can change group. it's made user.groups.clear() and user.groups.add(new_group) but it's not efficient, because there 2 sql query: delete, insert. how can change group update query? user , group related each other using manytomanyfield . means intersection table exists relating both entities, , if don't specify model map (using through attribute) django creates 1 you. looking @ sources django.contrib.auth.models see that's case. fortunatly, can access intermediary model using through attribute of manager (in case, user.groups.through ). can use regular model. example: >>> alice = user.objects.create_user('alice', 'alice@example.com', 'alicepw') >>> employee = group.objects.create(name='employee') >>> manager = group.objects.create(name='manager') >>> alice.groups.add(employee) >>> alice.groups.all() [<group: employee...
Read more

java - Need to add SOAP security token -

-
i need set custom soap header attribute on jax-ws generated webservice client. case webservice calls must go through proxy server requiring specific token (recieved web request header) present in soap request header. e.g.: 1 carserviceservice service = null; 2 service = new carserviceservice(new url(url), new qname(qname); 3 carserviceendpoint port = service.getcarserviceport(); it seems in line 3 wsdl retrieved , call fails due missing security token. 1 point direction on how done? a detailed example has been mentioned here: creating , deploying jax-ws web service on tomcat 6 this article shows how create , use security token .
Read more

java - EclipseLink JPA Object is not a known entity type -

-
i have strange problem eclipselink , object want persist. have 1 object (keypointlistimpl) stores object keypointimpl in list. persisting keypointimpl objects works great if try persist keypointlistimpl object java.lang.illegalargumentexception says object keypointimpl isn't known entity type. here keypointimpl code: @entity @table(name="keypoints") public class keypointimpl implements keypoint { @id @generatedvalue(strategy = generationtype.identity) private int id; @enumerated(enumtype.string) private detectortype keypointtype; private float x; private float y; private float size; private float angle; private float response; private int octave; private int classid; ... } here keypointlistimpl code: @entity @table(name="keypointlists") public class keypointlistimpl implements keypointlist { @id @generatedvalue(strategy = generationtype.identity) private int id; @onetoone(cascade={ca...
Read more