1

I'm trying a SEDE query to known the questions that has an accepted answer and with big difference of votes between the accepted answer as "best" answer.

My try of query is:

SELECT TOP 10 
    q.Title,
    (select max(m.Score) from Posts m where m.ParentId = q.Id ) as Maximum,
    a.Score as Accepted

FROM
    Posts q
  INNER JOIN
    Posts a ON q.AcceptedAnswerId = a.Id

WHERE
    q.PostTypeId = 1

order by Maximum-Accepted desc

however, following error appears:

Invalid column name 'Maximum'. Invalid column name 'Accepted'.

Looking for examples, I see similar ones, it seems the query is near than ok, but I do not see where is the error.

Any help is welcome.

  • Your SQL is wrong, this question is off topic here. For valid SQL for what you need, see this: stackoverflow.com/questions/12883753/…\ – Shadow Wizard Dec 25 '17 at 11:09
  • @ShadowWizard: it is obvious my sql is wrong, an error appears when I execute it. Moreover, what you call "valid SQL for what you need" is an example of something very unrelated (no inner select, ...), see at received answer. Finally, this is a query for stack exchange data using SEDE, SQL has lot of variants, I think it is on-topic. – pasaba por aqui Dec 25 '17 at 11:22
3

If you want to stay DRY add an extra query:

SELECT TOP 10
       Title
     , Maximum
     , Accepted
FROM
(
  SELECT
    q.Title,
    (select max(m.Score) from Posts m where m.ParentId = q.Id ) as [Maximum],
    a.Score as [Accepted]
  FROM
      Posts q
    INNER JOIN
      Posts a ON q.AcceptedAnswerId = a.Id
  WHERE
     q.PostTypeId = 1
) as data

ORDER BY ([Maximum]-[Accepted]) DESC

See it live here.

Based on the comments I decided to look at the queryplans for both queries:

This is the one for my query:

query plan for my query

And here is the one from Glorfindel

glorfindel

It looks like my query does one index scan less and due to that needs one hash match less. Who could have predicted that. SQL Server awesomeness confirmed ...

After I revisited this the following query only does two clustered_index_scan's so that should perform even slightly better.

  • I was always afraid that such queries would blow up, because the inner query has so many results, but SQL Server is smarter than I thought. – Glorfindel Dec 25 '17 at 12:10
  • @Glorfindel well, it looks like we can trust SQL Server. I honestly wouldn't have predicted that outcome .... – rene Dec 25 '17 at 12:31
  • Thabks for the answer. Why enclose some names in brackets? – pasaba por aqui Dec 25 '17 at 12:55
  • Just in case they turned out to be a reserved word @pasabaporaqui – rene Dec 25 '17 at 12:57
1

What helps is just writing out the definitions of the columns:

order by (select max(m.Score) from Posts m where m.ParentId = q.Id ) - a.Score desc

This is the resulting query.

  • Thanks a lot for the answer. No way to use "as" ? Rewrite the inner query seems something to skip. – pasaba por aqui Dec 25 '17 at 11:14
  • Not that I know of, but I'm not a SQL wizard. Like Shadow Wizard says, this is more of an (advanced) SQL question than a SEDE question. – Glorfindel Dec 25 '17 at 11:18

You must log in to answer this question.

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