Additional Sort IF some condition

DoubleDee · August 21, 2014, 10:54pm

I created a wrapper query to calculate percentages like this…


SELECT reviewTotal, agreeCount, agreeTotal, (agreeCount/agreeTotal) * 100 AS agreePct
FROM(
	SELECT and so on...

	) AS wrapper
	ORDER BY agreeCount DESC, created_on ASC

Everything is working fine, except for the following annoying data anomaly…

When the user chooses to display Comments sorted by “Agree” (Count), my query returns this result-set…


id	agreeCount	agreeTotal	agreePct	created_on
---	-----------	-----------	---------	-----------
16	5		5		100.0000	2014-07-01
33	2		4		50.0000		2014-07-05
19	1		4		25.0000		2014-07-15
2	0		2		0.0000		2014-08-01
7	0		0		NULL		2014-08-02
3	0		2		0.0000		2014-08-03
241	0		0		NULL		2014-08-04

The problem is that the order in which the zero agreeCounts are sorted is messing up my PHP formatting function, because when agreeTotal == 0 then it displays ‘–’, otherwise it displays ‘0’.

So I end up with…


Helpful: 0 (0%)
Helpful: --
Helpful: 0 (0%)
Helpful: --

Is there someway to tell MySQL this…

“Sort all Comments by “agreeCount” in descending order, AND if “agreeCount” == 0 then also sort by “agreeTotal” in descending order, otherwise don’t do an additional sort, AND after that go on ahead and sort by “created_on” in ascending order like the original query states.”

This is how I want the result-set to look…


id	agreeCount	agreeTotal	agreePct	created_on
---	-----------	-----------	---------	-----------
16	5		5		100.0000	2014-07-01
33	2		4		50.0000		2014-07-05
19	1		4		25.0000		2014-07-15
2	0		2		0.0000		2014-08-01
3	0		2		0.0000		2014-08-03
7	0		0		NULL		2014-08-02
241	0		0		NULL		2014-08-04

That way, my PHP will display Comments like this…


Helpful: 0 (0%)
Helpful: 0 (0%)
Helpful: --
Helpful: --

Displaying things this way will show the user Comments where people answered “Agree?” and some said “Yes” (i.e. agreeTotal = xx, agreeCount = xx), followed by people who answered “Agree?” and everyone said “No” (i.e. agreeTotal = xx, agreeCount = 0), followed by people who never even answered this question (i.e. agreeTotal = 0, agreeCount = 0, agreePct = NULL).

Hope that makes sense!

Sincerely,

Debbie

r937 · August 22, 2014, 2:11am

just use this –


ORDER
    BY agreeCount DESC
     , agreeTotal DESC
     , created_on ASC

DoubleDee · August 22, 2014, 3:16am

That isn’t what I described above…

Debbie

Jeff_Mott · August 22, 2014, 3:50am

Are you sure? Check again…

r937 · August 22, 2014, 9:46am

yes, i know

did you try it?

DoubleDee · August 22, 2014, 2:26pm

I’m sure.

(Looks like you and Rudy are having an “off” game…)

Debbie

Jeff_Mott · August 22, 2014, 2:29pm

That must be it.

I’m going to ask the same question as Rudy. Did you try it?

DoubleDee · August 22, 2014, 2:57pm

Here is a larger amount of data and why this suggestion will NOT work…

Original Results:

ORDER BY agreeCount DESC, created_on ASC


id	agreeCount	agreeTotal	agreePct	created_on
---	-----------	-----------	---------	-----------
5	3		3		100.0000	2014-08-01
9	2		3		66.6667		2014-08-02
4	2		3		66.6667		2014-08-03
30	2		5		40.0000		2014-08-03
8	1		2		50.0000		2014-08-04
6	1		3		33.3333		2014-08-05
1	1		1		100.000		2014-08-06
238	1		4		25.0000		2014-08-07
2	0		2		0.0000		2014-08-08
7	0		0		NULL		2014-08-09 <====
3	0		2		0.0000		2014-08-10
241	0		0		NULL		2014-08-11 <====

Desired Results:

ORDER BY agreeCount DESC, ???


id	agreeCount	agreeTotal	agreePct	created_on
---	-----------	-----------	---------	-----------
5	3		3		100.0000	2014-08-01
9	2		3		66.6667		2014-08-02
4	2		3		66.6667		2014-08-03
30	2		5		40.0000		2014-08-03
8	1		2		50.0000		2014-08-04
6	1		3		33.3333		2014-08-05
1	1		1		100.000		2014-08-06
238	1		4		25.0000		2014-08-07
2	0		2		0.0000		2014-08-08
3	0		2		0.0000		2014-08-10
7	0		0		NULL		2014-08-09 <====
241	0		0		NULL		2014-08-11 <====

Rudy & Jeff’s Suggestion:

ORDER BY agreeCount DESC, agreeTotal DESC, created_on ASC


id	agreeCount	agreeTotal	agreePct	created_on
---	-----------	-----------	---------	-----------
5	3		3		100.0000	2014-08-01
9	2		3		66.6667		2014-08-02
4	2		3		66.6667		2014-08-03
30	2		5		40.0000		2014-08-03
[COLOR="#FF0000"]238	1		4		25.0000		2014-08-07 <==== Moved out of place
6	1		3		33.3333		2014-08-05 <==== Moved out of place
[/COLOR]8	1		2		50.0000		2014-08-04
1	1		1		100.000		2014-08-06
2	0		2		0.0000		2014-08-08
3	0		2		0.0000		2014-08-10
7	0		0		NULL		2014-08-09 <==== OK
241	0		0		NULL		2014-08-11 <==== OK

Your suggestion fixes one problem, but creates a new one.

In the data above, there are 4 Comments which each received 1 “Agree” vote. My business requirement states that when there is a tie in the agreeCount, that things should be sorted chronologically (oldest-to-newest).

Your query takes 2 newer Comments and sorts them to the top of the 1 “Agree” vote sub-dataset which isn’t what I wanted.

Off Topic:

Admittedly, all of this is being pretty picky, but then again, there is no sin in wanting things exactly as we want them!

My business requirements were created in such a way as to display data in what I feel is the most intuitive way, so little anomalies like mess things up!

BTW, what makes this problem REALLY TRICKY is not the SQL, but the fact that my PHP is trying to use one query for 8 different sorts!

(My PHP is written in such a way that I can easily swap in and out what is in the WHERE and ORDER BY clause, but if I need completely different queries for each sort, then that is a gigantic PITA!!

And that is why I am hoping Rudy can come to my rescue again!!)

Is it possible to do what I want with minimal pain?? :-/

Sincerely,

Debbie

r937 · August 22, 2014, 3:36pm

sorry

arout77 · August 22, 2014, 3:54pm

Therein lies the problem. First rules of programming?

Take a large, complex problem and break it down into several smaller, manageable problems
Each action should do one thing, and one thing only, but do it very well

In other words, this sounds like a hot mess. I’d reconsider your plan of attack here…I anticipate a response along the lines of “too far into this to redo it”, but…if you’re having this much trouble just getting it working proper, I can only imagine what it will be like maintaining this over the long haul.

DoubleDee · August 22, 2014, 4:01pm

Don’t tell me I stumped Rudy?!

DoubleDee · August 22, 2014, 4:04pm

Let’s not blow things out of proportion. (Nothing in my database or my code base is a “hot mess”. Ever.)

First off, this is not a problem with my PHP.

I can’t write two queries and have my PHP pick one versus the other, because this is not a case where I need a certain type of query if there are zeros.

Rather, I need my query to look at the data - on real time - and if it sees a “0” in the agreeCount column, then it needs to do the additional sort, otherwise just sort but agreeCount and created_on.

That is why I posted in the database forum.

Here is hoping Rudy or someone else knows some more advanced SQL tricks that a mere mortal like me doesn’t use regularly.

And again, this have nothing to do with poor design or programming on my part, so let’s move beyond the “Debbie has another ‘hot mess’ talk…”

Sincerely,

Debbie

r937 · August 22, 2014, 5:18pm

no, you didn’t

i’m just not going to write your query for you again

DoubleDee · August 22, 2014, 5:30pm

You never wrote my query for me the first time.

Back to the old Rudy I see…

r937 · August 22, 2014, 5:43pm

oh for crying out pete’s sake, debbie

how many posts do you have on sitepoint? and how many of them were requests for queries? and how many of them did i answer?

hundreds, hundreds, hundreds

besides, i already gave you my advice in this thread (see post #2)

if you’re not happy with that solution, fine, but let’s keep the passive-aggressive attitude out of it, mkay?

DaveMaxwell · August 22, 2014, 5:54pm

I think using COALESCE will get you what you want:


 ORDER BY agreeCount DESC
 		, COALESCE (agreePct, -1) DESC
        , created_on DESC

DoubleDee · August 22, 2014, 6:38pm

You answer lots .

I just don’t understand why there is the “Nice Rudy” and the “Mean Rudy”…

The latter always seems to say, “I know the answer, and I want to make sure you know I have the answer, but I’m just not in the mood to help you because _____.”

Why be like that?

You go out of your way to consistently tell me that you do NOT want to help me, versus being like a normal person and just not posting.

If there ever was a textbook example of passive-aggressive behavior it has to be yours…

Yeah, and you solved the wrong problem.

if you’re not happy with that solution, fine, but let’s keep the passive-aggressive attitude out of it, mkay?

You live such a sad life Rudy…

DoubleDee · August 22, 2014, 6:42pm

Since Rudy couldn’t come up with a working answer today, here is my solution…


ORDER BY agreeCount DESC, 
CASE agreeCount 
WHEN 0 THEN agreeTotal END DESC,
created_on ASC

Works like a charm! (And it’s drama-free too!)

Thanks Debbie! :tup:

arout77 · August 22, 2014, 7:04pm

LOL

r937 · August 22, 2014, 8:54pm

and the moral of this thread –

when properly motivated, debbie can write her own queries

my job here is done

mods may close the thread now