What is meta? ×
Meta Stack Exchange is where users like you discuss bugs, features, and support issues that affect the software powering all 127 Stack Exchange communities.

I put quite an effort into an answer to a question that was not very clear. Also helped him to clarify and simplify. I think I even answered it for the most part and added information on why and how. Nobody else even tried. That's why the author could still delete his question.

That's what he did today: withdraw his question, and posted a new version. The new version is clearer, he picked up a couple of my points and added more information. He is new to the site, so I do not assume any evil intent, he just wasn't sure how to do this.

Well, I am still rather new myself. So I wonder: is there a way to get to my deleted answer. All the work that has gone into it .. and it should cover most of what is needed for the question. I have studied a couple of similar posts, but there does not seem to be a way for a low-privilege user.

I think everybody should be able to see their own questions, answers and comments. Especially if there was nothing wrong with them to begin with! When postings get deleted, they should be moved to a private area of the author. Maybe with the exception of offensive stuff and spam.

The new question:
http://stackoverflow.com/questions/7492396/how-to-group-sub-queries

The removed question:
http://stackoverflow.com/questions/7478729/how-to-group-multiple-queries-by-similar-columns/7479053

share|improve this question
2  
I agree that you should always be able to see your own questions and answers, I'm not sure what's gained by hiding them either. –  Matthew Read Sep 20 '11 at 23:49
    
Heh and the new question is deleted. –  Jeff Mercado Sep 20 '11 at 23:50
    
Wow that was fast. the author has withdrawn again :-o –  Erwin Brandstetter Sep 20 '11 at 23:52
add comment

1 Answer

up vote 7 down vote accepted

It's not possible for you to retrieve your answer by yourself*. Since I'm 10k, I've copied your post for you:

Slightly confusing, you are right there. I am also not sure, why you computed a and b in separate queries as the WHERE clause is the same. From what you say and what I guess, this might be close to a solution:

Disclaimer: I have not actually run the query, don't know the table structure and data, so this is untested.

WITH myids AS (
  SELECT m.thread_id 
    FROM jivedw_message m
    JOIN jivedw_container c USING (container_id)    -- these ..
   WHERE c.creation_ts > '2009-11-19'::date         -- .. lines my

be redundant. GROUP BY 1 HAVING COUNT(m.message_id) = 1)

SELECT c.container_id
      ,a.a AS "Total Number of Un-Replied-To Threads"
      ,a.b AS "Average Age"
      ,b.c AS "1 Day"
  FROM jivedw_container c
  LEFT JOIN (
  SELECT m.container_id
          ,count(m.thread_id) AS a
          ,avg(date_part('days', current_date-99 - m.creation_ts)) AS b
    FROM jivedw_message m
    JOIN myids USING (thread_id)
   GROUP BY 1) a USING (container_id)
  LEFT JOIN (
  SELECT m.container_id
          ,count(m.thread_id) AS c
    FROM jivedw_message m
    JOIN myids USING (thread_id)
   WHERE date_part('days', current_date-99 - m.creation_ts) = 1
   GROUP BY 1) b USING (container_id)
 WHERE c.creation_ts > '2009-11-19'::date
 ORDER BY 1;

Among other things ..

  • aliases to simplify code
  • remove unnecessary brackets to simplify code
  • CTE (WITH clause) for the repeated use -> simpler code, faster
  • lumped a & b together as they have identical WHERE clause
  • JOIN .. USING -> simpler code
  • LEFT JOIN .. so we still get a result if one value is missing
  • use ISO 8006 date format to make it independent of DateStyle

* Actually, if you know the post ID then you can go to the edit page, which still works. I can see the deleted question so I can tell you that you can still see the content of your post at http://stackoverflow.com/posts/7479053/edit:

share|improve this answer
    
"This should be community wiki." :) –  Jeff Mercado Sep 20 '11 at 23:50
    
Thank you. This time I copied it in case it gets deleted again. :) –  Erwin Brandstetter Sep 20 '11 at 23:53
1  
Wow, I didn't realize we could recover content of deleted posts like that. –  Jeff Mercado Sep 21 '11 at 0:08
    
@JeffMercado: Related: How is the editing of deleted questions supposed to behave?. –  Jeremy Banks Sep 21 '11 at 0:12
add comment

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .