How to combine subquery inside from vs from table_name?

Question

1.00/5 (1 vote)

See more:

Hi,

I need to select just one columns among those returned by a Select statement and I'm trying to use a subquery inside FROM in order to do that.

My problem is that the column is not recognized outside the subquery; on the other hands, I'm not allowed to use another FROM statement to tell the name of the table I want to use (and there is no need to do that because such FROM TableName is already inside the subquery I need to extract the column from, right?).

What I have tried:

SELECT Day(MyDate) AS MyDate
 FROM
 (
  SELECT
       TOP(10) 
       COUNT(*) AS 'total',
       day(myDate) AS 'myDay'  
  FROM tableName 
  GROUP BY Day(MyDate)
  ORDER BY 'total' 
  ) AS MyDate

Error: Invalid column name  'MyDate'

Posted 5-Nov-20 23:15pm

xhon

Updated 6-Nov-20 1:32am

v2

Add a Solution

Comments

[no name] 6-Nov-20 5:48am

You changed it to 'myDay'.

xhon 6-Nov-20 6:00am

I edited it, but nothing changed, the column name is still invalid

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Accepted Answer · 2020-11-06T01:33:00

Your sub-query returns two columns: myDay and total.

There is no column called MyDate to access in your outer query.

Try:

SQL

SELECT T.myDay, T.total
FROM
(
    SELECT TOP(10) COUNT(*) AS 'total', day(myDate) AS 'myDay'  
    FROM tableName 
    GROUP BY Day(MyDate)
    ORDER BY 'total' 
) As T

However, in this instance, you don't need the outer query at all:

SQL

SELECT TOP(10) COUNT(*) AS 'total', day(myDate) AS 'myDay'  
FROM tableName 
GROUP BY Day(MyDate)
ORDER BY 'total'