Mysql - Can I use the 'having' clause after 'order by' clause?

Question

For an application I make, I need to get the last week (monday to friday) and the details for every day for each user I have in the database.

For now, my query gets every day of the month starting to today (So if we're the 18th, it'll get 18-17-16... and so on) and the month before (in case we're the first...)

But I want to limit the select to the first 8 days. I read this:

http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/

The last example is what I'm trying to do. But the problem is that I need to do an order by before. I need to order by months (DESC), than by days (DESC) and than I need to do the having to get what I want. I tried it without ordering, it works well, but it gets the wrong days. Because by default it's ordered ASC.

Just for the example, here's a query that represent my problem, here's what I have:

SELECT 
    day, id 
FROM 
    myTable 
WHERE 
    (month = EXTRACT(MONTH FROM CURRENT_TIMESTAMP) - 1 OR 
         month = EXTRACT(MONTH FROM CURRENT_TIMESTAMP) - 2) AND 
    year = EXTRACT(YEAR FROM CURRENT_TIMESTAMP) AND 
    day <= EXTRACT(DAY FROM CURRENT_TIMESTAMP) 
ORDER BY 
    month DESC, 
    day DESC;

Here's what I want:

set @day := 0, @id:= '';

SELECT 
    day, id, @day := if(@id= id, @day + 1, 1) as day_number,
    @id := id as dummy 
FROM 
    myTable 
WHERE 
    (month = EXTRACT(MONTH FROM CURRENT_TIMESTAMP) - 1 OR 
         month = EXTRACT(MONTH FROM CURRENT_TIMESTAMP) - 2) AND 
    year = EXTRACT(YEAR FROM CURRENT_TIMESTAMP) AND 
    day <= EXTRACT(DAY FROM CURRENT_TIMESTAMP) 
ORDER BY 
    month DESC, 
    day DESC 
HAVING 
    day_number <= 8;

But I can't put a HAVING after an ORDER BY. If I remove the ORDER BY clause, I'll get all days for every id between 1st and 8th. But I need between 18th and 10th. (18th being the current date)

With the code I have in the app, it will stop after a week, so it doesn't matter if the select returns sunday to saturday, I just want to have at least monday to friday. Which is why I took the number '8', because the app will execute this only mondays.

Thanks for any help/suggestion!

Giorgos Betsos · Accepted Answer · 2016-04-18T14:25:30.547

0

You can wrap your query in a subquery and perform filtering in the outer query:

SET @day := 0, @id:= '';

SELECT day, id, day_number
FROM (
  SELECT 
      day, id, 
      @day := IF(@id = id, @day + 1, 
                  IF(@id := id, 1, 1)) as day_number
  FROM 
      myTable 
  WHERE 
      month = EXTRACT(MONTH FROM CURRENT_TIMESTAMP) - 1 OR 
      month = EXTRACT(MONTH FROM CURRENT_TIMESTAMP) - 2) AND 
      year = EXTRACT(YEAR FROM CURRENT_TIMESTAMP) AND 
      day <= EXTRACT(DAY FROM CURRENT_TIMESTAMP) 
  ORDER BY 
    month DESC, 
    day DESC ) as t
WHERE  day_number <= 8;

The outer query uses a WHERE clause to perform filtering. HAVING is used in conjunction with GROUP BY and operator on group level.

Note: The above query uses nested conditionals in order to properly read/write @id variable.

edited Apr 18 '16 at 14:25

answered Apr 18 '16 at 13:53

Giorgos Betsos

71,379
9
63
98

Thanks a lot, I'll try it! I never thought about a subquery! – anthomaxcool Apr 18 '16 at 14:01
Is it possible that the way `day_number' is calculated is wrong? It always returns 1 – anthomaxcool Apr 18 '16 at 14:17
@anthomaxcool Have you used your query to set `@id` or my version? – Giorgos Betsos Apr 18 '16 at 14:23
Oh and `AS something` is missing after the from. If there's no `AS` it returns an error found here: http://stackoverflow.com/questions/1888779/every-derived-table-must-have-its-own-alias – anthomaxcool Apr 18 '16 at 14:23
Never mind, the counts works fine, I must have missed something while writting it. But it won't return 18 to 10, it still returns 1 to 8... I don't get why – anthomaxcool Apr 18 '16 at 14:34
Actually I think `day_number` works like it's suppose to. If I keep the code inside the first `FROM`, it will show that `day_number` = `day`, like 18=18, but I need 18 = 1, 17 = 2 until `day_number` reaches 8. Can we do this backward? – anthomaxcool Apr 18 '16 at 14:40
I know the question is asking `having` with `order by`, your answer is answering that, thanks fort that! But I found the solution to my problem. I removed everything from my `where` clause and replaced it with `(STR_TO_DATE(CONCAT(t.year,'-',t.month + 1,'-',d.day), '%Y-%m-%d') BETWEEN (NOW() - INTERVAL 8 DAY) AND NOW())` – anthomaxcool Apr 18 '16 at 17:11

Mysql - Can I use the 'having' clause after 'order by' clause?

1 Answers1