I had a question it was pretty simple and clean cut. None of the comments were on clarity of the question at hand. I asked,

How can I generate a row_number without using a window function?

  • The question wasn't totally downvoted.. It had one upvote.
  • The question wasn't specific to just me. While I didn't address performance implications, I provided content that could later be used to address performance implications

    @MaxVernon I would countervote if I could - because it is a common DBA problem - identify a good technical PK for duplicate removal. And for huge tables some solutions dont work well – filiprem 2 days ago

  • Yet others have stated "there's more than one way of doing something". Is that not useful? What's wrong with wanting to learn another way?
  • I provided two quality contributions to the site which I found very interesting and learned a lot from,
    1. One of them used an array of a UNIQUE column, and a series as an index into the array.
    2. One of them used a row-comparison with a query in a subselect <=

The only person that spoke up for the question, filiprem has the badge. What's wrong with the question?

We should be careful to confuse these x-y problems, with someone wanting to learn x or asking a legitimate question.

  1. Window functions can't be used in joins.
  2. They can't be used in ALTER statements.

That's not to say you can't make it happen with sub-SELECTs. I was playing around and wanted to try a different method with generate_series and was having a difficult time getting it to work. I knew it was possible, but didn't know how. This seemed like a good place to ask.

  • 1
    I don't know, "Please explain your answers" comes off to me as "Do my homework and be thorough about it." Answers by definition should be explanatory enough to be useful, as a matter of course. – Aaron Bertrand Jan 3 '17 at 0:45
  • 5
    Having said that, I didn't vote on the question (since it involves a platform I care very little about), nor did I participate in its closure. But obviously there was consensus among members of our community, including a moderator, that your question was too localized (you can RTM there). Just because you don't agree doesn't make them wrong. – Aaron Bertrand Jan 3 '17 at 0:47
  • Understandable confusion on homework (though that it even could be homework would show it's not too localized and it serves another purpose), but what I was trying to do was eliminate poor answers ... like this one which are upvoted bandwagon answers of very poor quality that don't endeavor to explain themselves at all. – Evan Carroll Jan 3 '17 at 2:25
  • 7
    I had a look at the question and I like that it's presented clearly. You are doing a great job at that. But the question did not seem interesting. The restrictions you placed seem arbitrary and I don't see the use case. ctid is the poor-man's surrogate for an ad-hoc PK. That can be found in various related answers.. I quickly decided to .. do nothing there. I wouldn't fight so hard for it. Be diplomatic and move on. Redirect your energy to more of your good questions. – Erwin Brandstetter Jan 3 '17 at 3:53
  • 2
    I'm not confused. I know it isn't homework. Your tone in that question makes you sound like the entitled kid whose mommy does his homework for him, that's all. – Aaron Bertrand Jan 3 '17 at 5:40
  • 3
    Can you explain what you mean by "Window functions can't be used in joins" and "They can't be used in ALTER statements" — either with an example or link to the docs? – Jack says try topanswers.xyz Jan 3 '17 at 8:41

I was the first person who voted-to-close the question.

However, I did not do that as a result of me being a "SQL Server guy" 1, and you're not.

I took that action because, in my opinion, the question is not likely to be of any great value to future visitors since it explicitly denies use of the most commonly accepted methods of performing the task in question, without positing a valid reason for excluding those methods.

I cannot mod-close a question since I'm not a moderator - 4 other people also had to agree in order for the question to be closed. The question was open for 57 hours during which time many people(100+ views) had the option of voting up the question, which did not happen, save once, perhaps because "This question does not show any research effort; it is unclear or not useful". Had the community reached the conclusion the question was interesting, well researched, posed a potentially interesting answer, and was worthy of spending time and effort answering, they would have done so.

The close reason, which was obvious to me, was "too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users." There are no obvious typos, or basic errors, which leaves, by reason of deduction, only that the question is "not relevant". You could perhaps make the question relevant by adding details so it is relevant; if you did, I might be inclined to vote to re-open it. Perhaps some valid, logical, expedient reason exists for not using the commonly available ROW_NUMBER() aggregate, joining to a numbers table, using ctid, using temp tables, or only doing this in a single statement. Your post still does not deign to answer that, simply because you don't want to.

As for your answers, I fail to understand why using array_agg() or generate_series() is allowed, while row_number() is not.

  • That's not what I'm trying to do. CREATE TABLE foo (k int); CREATE TABLE bar () INHERITS (foo); CREATE TABLE baz () INHERITS (bar); INSERT INTO bar VALUES (42); INSERT INTO baz VALUES (666); SELECT ctid, k FROM foo – Evan Carroll Jan 3 '17 at 22:58
  • 1
    Can you respond to @JackDouglas' question about why row_number can't be used in joins, and all the other questions posed to you? Be good to us, and we'll be great to you. – Max Vernon Jan 3 '17 at 23:26

You must log in to answer this question.

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