Sorting within GROUP BY? 2

[Actorial]

Hi There,

I've been working hard at this for a while, and I've figured a few things out, but I'm having trouble sorting the below query by "mls_condos.date_posted":

"SELECT SQL_CALC_FOUND_ROWS owyw.*,mls_condos.name,MAX(mls_condos.date_posted),COUNT(mls_condos.date_posted) 
FROM owyw 
LEFT JOIN mls_condos ON mls_condos.name=condo_names.mlsname 
JOIN condo_names ON owyw.development=condo_names.mcbname GROUP BY owyw.development 
ORDER BY mls_condos.date_posted DESC"

It doesn't sort properly. I want it to sort "owyw.development" by "mls_condos.date_posted" from newest to oldest. I also don't want to show duplicates of "owyw.development". Just each "owyw.development" ordered by the corresponding most recent "date_posted". I think I'm close, but lack a little understanding with GROUP BY and ORDER BY.

Thank You in Advance!

 

[rac2]

SELECT owyw.development,
       mls_condos.name,
       MAX(mls_condos.date_posted),
       COUNT(mls_condos.date_posted)

FROM owyw
JOIN condo_names 
  ON condo_names.mcbname = owyw.development
LEFT JOIN mls_condos 
       ON mls_condos.name=condo_names.mlsname

GROUP BY owyw.development, mls_condos.name

ORDER BY MAX(mls_condos.date_posted) DESC

Try the above and let me know how it matches your requirement.

Although MySQL allows columns in a SELECT list which are not in the GROUP BY list, that is not standard SQL and can produce strange results. I have added the grouping column to the SELECT list and the SELECT list column to the GROUP BY.

I have re-arranged the JOIN clauses to show that the basic table is owyw; it has a column which can be linked to condo_names; condo_names has a column which can be linked to mls_condos; mls_condos may not have a matching row so we use the LEFT JOIN.

The ORDER BY list should use only columns in the SELECT list. Again, this is standard SQL, it may not generate an error in MySQL, but the result may not be what is expected.

 

[Actorial]

Thanks for your reply. I tried the above code and it spat out an error: "Invalid use of group function." Hmmm.

 

[Actorial]

Ok i got no errors withthe code now by removing MAX() frm the last line, but it still doesn't seem to sort in any order that I can discern

SELECT owyw.development,
       mls_condos.name,
       MAX(mls_condos.date_posted),
       COUNT(mls_condos.date_posted)

FROM owyw
JOIN condo_names 
  ON condo_names.mcbname = owyw.development
LEFT JOIN mls_condos 
       ON mls_condos.name=condo_names.mlsname

GROUP BY owyw.development, mls_condos.name

ORDER BY mls_condos.date_posted DESC

 

[r937]

select owyw.development
     , mls_condos.name
     , max(mls_condos.date_posted) [b]as max_date[/b]
     , count(mls_condos.date_posted) [b]as post_count[/b]
  from owyw
inner  
  join condo_names 
    on condo_names.mcbname = owyw.development
left outer
  join mls_condos 
    on mls_condos.name = condo_names.mlsname
group 
    by owyw.development
     , mls_condos.name
order 
    by [b]max_date[/b] desc

r937.com | rudy.ca

 

[Actorial]

That was awesome! It works perfectly! I understand most of what you did, basically making aliases with the AS statements. I will disect this and learn, only wish I had something to offer in return...

 

[rac2]

...wish I had something to offer in return...

I happen to know that Rudy will work for stars.

 

[Actorial]

alright i gave you both stars, i could not have figured that out in any reasonable amount of time on my own.