Sorting and grouping 1

[imstillatwork]

There is no way to sort each group of results differently in a single query is there?

That probably does not make sense, here's what I'm doing:

select category.title,category.sortContentBy,content.title
from category
left join content
on content.category_id = category.id

category.sortContentBy is 1 or 0
0 being sort by content.date_added
1 being sort by content.sort_order

Is there any way to sort each left join dynamically on a per category basis? so all the content items matching the join are sorted by that categories specified sort-order?

It's a long shot, but what I HATE to have to do is output all the categories and run a query for each to get the content. The content in each category will need to be sorted in a specific way, which is saved in the category.sortContentBy field...






Kevin

http://www.lot-o-nothin.com/cfml/

Phase 1: Read the CFML Reference
Phase 2: ???
Phase 3: Profit!

 

[hvass]

If you turn your sort criteria into numerics I think you can. But I may be missing the point...

If I get this right you want a sort term whereby for category A you need to sort by date_added and for category B you need to sort by a different criteria namely sort_order

The category.sortContentBy table would need to have a number of columns in with 1 or 0

category_id, date_added, sort_order
A,1,0
B,0,1

Then your sort term would be something like
order by
category_id,
to_days(date_added)*sortContentBy+sort_order*sortContentBy

So the A category comes first it gets sorted by
date_added*1 + sort_order*0
Category B comes next and gets sorted by
date_added*0 + sort_order+1

 

[r937]

what do you get when you multiply a date by a number?

how about...

order by
case when category.sortContentBy = 0
then content.date_added
else content.sort_order
end



r937.com | rudy.ca

 

[hvass]

yes have to be careful to express everything as a number so you have to use the to_days() function

but rudy I humbly bow to your superior solution did not occur to me you might be able to do a case statement as part of an order by...

 

[imstillatwork]

So rudy, will that sort PER category or sort the entire query results?

I'm not sure I made the question clear. here would be an example of the output. BTW there is only two tables.

category (id,title,sortContentBy)
and
content (id,category_id,title)

I was hoping for output from one query like so:

cat_title | sortContentBy | content_title | sort_order | date_added |
---------------------------------------------------------------------
CategoryA 			1						contenta1 					1					12/12/06
CategoryA 			1						contenta2 					2					11/10/06
CategoryA 			1						contenta3 					3					01/02/07
CategoryB 			0						contentb1 					2					01/10/05
CategoryB 			0						contentb2 					3					15/34/06
CategoryB 			0						contentb3 					1					01/02/07

where if sortContentBy = 1, that category content is sorted by the sort_order field, and date_added field is ignored
or if sortContentBy = 0, that category content is sorted by the sortdate_added field, and sort_order fieldis ignored

Kevin

http://www.lot-o-nothin.com/cfml/

Phase 1: Read the CFML Reference
Phase 2: ???
Phase 3: Profit!

 

[imstillatwork]

Or is it only possible to sort the entire result, and not sort parts of it differently?

I'll play with your example Rudy, thanks.



Kevin

http://www.lot-o-nothin.com/cfml/

Phase 1: Read the CFML Reference
Phase 2: ???
Phase 3: Profit!

 

[imstillatwork]

Rudy: That seems like it works, but when I try to change it:

CASE WHEN cat.sortContentBy = 0
THEN c.content_date DESC
ELSE c.sort ASC
END


it fails. (errors)

"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DESC ELSE c.sort END' at line 15"

I'm sure I'm missing something fundamental here...

Kevin



Kevin

http://www.lot-o-nothin.com/cfml/

Phase 1: Read the CFML Reference
Phase 2: ???
Phase 3: Profit!

 

[r937]

the syntax is

ORDER BY expression [ASC|DESC]

in your case (no pun intended ;-)) the expression is the CASE expression

i.e. the ASC|DESC goes after it

perhaps you could do this --

ORDER BY
CASE WHEN cat.sortContentBy = 0
THEN c.content_date
ELSE [red]-c.sort[/red] END
DESC


r937.com | rudy.ca

 

[imstillatwork]

I believe that is correct for my example, but my example was wrong so I used

ORDER BY
CASE WHEN cat.sortContentBy = 0
THEN -c.content_date
ELSE c.sort END
ASC

What exactly is the - doing? I can see what it does, but can you explain why it is used here?

Kevin

http://www.lot-o-nothin.com/cfml/

Phase 1: Read the CFML Reference
Phase 2: ???
Phase 3: Profit!

 

[r937]

actually, come to think of it, the CASE expression should probably be using at least one CAST to ensure that both the THEN and ELSE expressions are the same datatype

the negative sign is an arithmetic operator

if you have rows with a numeric column that has values 9, 37, 11, and 42, and you wanted these rows in ascending sequence by those values, you could use ASC, or else you could use the negative of the number and order them in DESC sequence and get the same result

in your case you'd want to consider using the TO_DAYS function on your date, which produces an integer, so that you're dealing with apples and apples, since it's harder turning c.sort into a date

i wouldn't use the negative of a date, but i would use the negative of a number

assuming that c.sort is a number

r937.com | rudy.ca

 

[imstillatwork]

makes perfect sense!

Kevin

http://www.lot-o-nothin.com/cfml/

Phase 1: Read the CFML Reference
Phase 2: ???
Phase 3: Profit!

 

[imstillatwork]

So from what I've read (other places), all the expressions in the CASE statement should be the same type, mixing types in the difference conditions of the case expression doesn't always work so well? is that why, regardless of the minus sign, it just doesn't behave if I try to sort by date or sort_order (yes, integer) - but if I change the date to and int, it works wonderfully? The only problem I have had now is that to_days() is not very accurate, I mean, several items end upin the same to_days() value...posted on the same day. For now, I also sort by id, since the newer id is always larger, i can use it to help sort those by date also, but I don't feel that's the best way.

I tried date_format(content_date,%Y%m%d, etc) to make the date only a number, but it fails to sort properly, I'm guessing because the resulting number is too large - 14 digits, and using CAST( AS UNSIGNED) turns it into something like 2.007010512E+013 that does not sort as expected.

SOOOOoo....

After reading that the case statement might not like expressions of different types....

I did:

CASE WHEN cat.sortContentBy = 0
THEN c.content_date
END DESC,

CASE WHEN cat.sortContentBy = 1
THEN c.sort
END ASC

And everything is seems perfect. The dates are sorted as dates (datetime) and the sort_order is sorted as integers.

Woohoo!!


Kevin

http://www.lot-o-nothin.com/cfml/

Phase 1: Read the CFML Reference
Phase 2: ???
Phase 3: Profit!

 

[r937]

my apologies, i assumed that a column called "content_date" would contain dates, whereas if it contained datetimes, it would've been named "content_datetime" or something...

so instead of TO_DAYS, use UNIX_TIMESTAMP, and go back to just one CASE expression



r937.com | rudy.ca

 

[imstillatwork]

ha!

I browsed the mySQL date / time function page for 20 minutes and missed that? thanks again!

PS: I'll keep better watch of my column naming

Kevin

http://www.lot-o-nothin.com/cfml/

Phase 1: Read the CFML Reference
Phase 2: ???
Phase 3: Profit!