Need to get one more column in the SELECT

select Max(ChartCount)
from
(
select
Month(last_date) as [Mth],
Count(distinct Chart_No) as [ChartCount]
from Treatment
where last_date >= '2011-01-01'
and last_date < '2012-01-01'
Group by Month(last_date)
) a


In above, how do I include Month(last_date) as [Mth]?

Thanks,
Ashok
LVL 13
AshokSr. Software EngineerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Philippe DamervalSenior Analyst ProgrammerCommented:
Try this:
select Mth, ChartCount
from
(
select
Month(last_date) as [Mth],
Count(distinct Chart_No) as [ChartCount]
from Treatment
where last_date >= '2011-01-01'
and last_date < '2012-01-01'
Group by Month(last_date)
) a
WHERE ChartCount = (
select Max(ChartCount)
from
(
select
Month(last_date) as [Mth],
Count(distinct Chart_No) as [ChartCount]
from Treatment
where last_date >= '2011-01-01'
and last_date < '2012-01-01'
Group by Month(last_date)
) )

HTH

Philippe
0
AshokSr. Software EngineerAuthor Commented:
Philippe,

select Mth, ChartCount
from
(
select
Month(last_date) as [Mth],
Count(distinct Chart_No) as [ChartCount]
from Treatment
where last_date >= '2011-01-01'
and last_date < '2012-01-01'
Group by Month(last_date)
) a
WHERE ChartCount = (
select Max(ChartCount)
from
(
select
Month(last_date) as [Mth],
Count(distinct Chart_No) as [ChartCount]
from Treatment
where last_date >= '2011-01-01'
and last_date < '2012-01-01'
Group by Month(last_date)
) b ) ;

You were missing b in the second select, otherwise your solution is working.

Thanks,
Ashok
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Philippe DamervalSenior Analyst ProgrammerCommented:
The "b" should not be needed, as no fields from the subquery it refers to are used anywhere else in the query. The statement should work without it. If the table can be provided with obfuscated data I am confident I can resolve whatever issue was making it seem like it was required.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

AshokSr. Software EngineerAuthor Commented:
without b
in SQL Server 2000

I get

Server: Msg 170, Level 15, State 1, Line 23
Line 23: Incorrect syntax near ')'.

Thanks,
Ashok
0
AshokSr. Software EngineerAuthor Commented:
After adding b, it works.

The error is gone.

Ashok
0
Anthony PerkinsCommented:
If the table can be provided with obfuscated data I am confident I can resolve whatever issue was making it seem like it was required.
There is no need, 10 seconds testing in SSMS would tell you it does not compile without the alias.  You get the following error message:
Incorrect syntax near ')'.
0
Chris__WCommented:
The "missing b" is entirely unnecessary.  Aliases are not required, nor is he referencing any.
0
AshokSr. Software EngineerAuthor Commented:
It could be that SQL Server 2000 is buggy.

and

you may be correct if I was using SQL Server 2005 or 2008.

Ashok
0
Anthony PerkinsCommented:
Chris__W,
The "missing b" is entirely unnecessary
Please do your homework first.  It is very simple:
1. Copy
2. Paste
3. Execute
0
Philippe DamervalSenior Analyst ProgrammerCommented:
I'm surprised, but happy nonetheless that it is working for you. I don't have Sql Server 2000 to do any testing on so will file that one in my brain under "weird". It will be in good company.
0
Anthony PerkinsCommented:
I don't have Sql Server 2000 to do any testing on so will file that one in my brain under "weird".
That is the way SQL Server 7, 2000, 2005, 2008, 2008-R2 and 2012 operate.  You can test it for yourself using SSMS and I swear it will not take you more than 10 seconds to become a believer.
0
Anthony PerkinsCommented:
Did I mention it is all documented in SQL Server's BOL?  You can see for yourself by going to the FROM clause entry.
0
AshokSr. Software EngineerAuthor Commented:
To make it work for both SQL Server 2000 and SQL Server 2008,

why can't we add the "b"?
How long does it take to type one character?

Does it work in SQL Server 2005 or 2008 if you have the unnecessary "b"?

Thanks,
Ashok
0
Anthony PerkinsCommented:
Here is the relevant section for those that do not like reading BOL:
http://msdn.microsoft.com/en-us/library/ms177634.aspx
[AS] table_alias
Is an alias for table_source that can be used either for convenience or to distinguish a table or view in a self-join or subquery. An alias is frequently a shortened table name used to refer to specific columns of the tables in a join. If the same column name exists in more than one table in the join, SQL Server requires that the column name be qualified by a table name, view name, or alias. The table name cannot be used if an alias is defined.

When a derived table, rowset or table-valued function, or operator clause (such as PIVOT or UNPIVOT) is used, the required table_alias at the end of the clause is the associated table name for all columns, including grouping columns, returned.
0
Anthony PerkinsCommented:
ashok111,

The alias is required when using T-SQL.  Period.

Let's move on.
0
Philippe DamervalSenior Analyst ProgrammerCommented:
acperkins:
I have used subqueries to extract column values or criteria many times in SQL server, for years, without ever needing an alias. For instance:

select col1, col2, col3 = (subselect...) FROM table...
or
select col1, col2 from table where col1 = (subselect....)

Therefore, on top of being condescending, categorical and obnoxious, you are also wrong.
Please respect the other experts on this forum. We are here to help the users, not to insult one another. Your tone has no place in this exchange.

Ashok111:
Despite what you may read here, things are clearly more complex than they appear. I'm happy to do some research if you're really interested.
Thanks!

Philippe
Philippe
0
Anthony PerkinsCommented:
Philippe,

I have used subqueries to extract column values or criteria many times in SQL server, for years, without ever needing an alias.
I never mentioned subqueries, and I am afraid you are not using a subquery.  It is a derived table that is why you need an alias.

Therefore, on top of being condescending, categorical and obnoxious, you are also wrong.
Before making a fool of yorself, please take the time to test.  I promise it will not take you 10 seconds.  As you can see everyone else has done it already and are now convinced.  Unfortunately, certainty makes me categorical and stuborness to realize you are wrong makes me resort to being condescending.  So I will apologize, if you can tell me what would be an appropriate way to demonstrate it to you.  I have tried posting the error message, I have tried documentation, what else can I possibly do?
0
Anthony PerkinsCommented:
Your tone has no place in this exchange.
If you feel any of my comments are inappropriate, take it up in Community Support and lets just discuss the facts here.

P.S.  Most people here call me by my name: Anthony
0
Chris__WCommented:
Wow, my homework eh?  Copying faulty code and going soley by what SSMS lists as the issue is not what I'd consider 'homework'.  You may want to look past that, and it might take more than 10 seconds.  Surely you're aware that an extra or missing ')' will get you that same type of error, right?

If you've written actual code, you're also aware that this statement has issues:

"The alias is required when using T-SQL.  Period."

Excellent tact by the way.  I see you pass on these types of quality responses regularly here.  Regardless, if he's happy with the 'b', I'm sure you'll get your assist points (especially for the copy-paste steps) and can move on...  Clearly he'll have better results with actually fixing his code by working with damerval.  I'm going to try and forget this chain altogether, as just typing this wasted even more time.

Good luck with the code Ashok111.
0
Anthony PerkinsCommented:
Grief!  All EE is asking is that when we post solutions we test them.  Clearly the solution posted was not tested and in fact fails to compile. The author did the correct thing by pointing that out and kindly posting the correct solution for them and he/she is told they are wrong?  What is it with you guys?  Is it such an effort to test your code?  If you feel the code could have been improved (and I agree with you there) than post a better solution.  But in the meantime leave the author alone, they are right, they did the right thing.

Surely you're aware that an extra or missing ')' will get you that same type of error, right?
But that is not the case here.  See for yourself.  The author did not add or substract any, all they did was add the missing alias.
0
Chris__WCommented:
I can't beleive I'm going to type another reply, but here it is.   Feel free to reply with whatever bs you'd like, as I'm closing this tab and the monitoring is already off.

Nice redirect - No one has any issue with the author, not only did he get it working -  he even used an alias to begin with.  I've not seen many cases, outside of a derived table in conjunction with a UNION statement, where an alias actually caused a problem.  However, if that's not the core of the issue, they may be moving on with code that has more fundamental issues.  Something damerval is offering to help with.

Redirecting attention from the issues with your tone and remarks doesn't really help though when I can still scroll up and read them.  Quotes are neat though....

Here's a fun one you might have thought about earlier on:
and lets just discuss the facts here.
0
AshokSr. Software EngineerAuthor Commented:
acperkins,

You are right.  I tested it with SQL Server 2000.
And it did not work without fixing it a little.

I also said that alias may or may not be needed in SQL Server 2008.  I still have to confirm it which I will do later at home.
I have to create the table, insert data and test the same SELECT without the alias "b".

Thnaks,
Ashok
0
AshokSr. Software EngineerAuthor Commented:
Hell everyone,

In SQL Server 2008......

I just tried.
I did not even have to execute the SELECT statement to figure out if alias "b" is needed or NOT.

After just copying and pasting it, SQL Server Manager Studio gave error immediately on last closing ")".
The exact error is
Incorrect syntax near ")". Expecting AS, ID or QUOTED_ID.

By the way, I ignored the error and click the Execute

and

Msg 102, Level 15, State 1, Line 23
Incorrect syntax near ')'.

Above error showed up.

This confirms that it is not only SQL 2000 that requires the alias "b".

Thanks,
Ashok
0
AshokSr. Software EngineerAuthor Commented:
And here is the screenshot
from
SQL Server (2008) Manager Studio.
Proof.png
0
Anthony PerkinsCommented:
ashok111,

This confirms that it is not only SQL 2000 that requires the alias "b".
Exactly, it is required and always has been.  Perhaps I should have done the same thing (posted a screenshot from each environment) when i originally tested it and there would not have been any misunderstanding.

I am sorry you had to go through all this and glad you figured it out.

Best of luck.
0
AshokSr. Software EngineerAuthor Commented:
acperkins,

I feel like you deserve some points.
For me, it's not about points, but learning exactly what is CORRECT.  Knowing if there are any differences between SQL 2000 & SQL 2008 is very important for me so my time is not wasted.

Thanks,
Ashok
0
AshokSr. Software EngineerAuthor Commented:
acperkins,

Let me know if you like me to object and give some points to you.

Thanks,
Ashok
0
CboudrozCommented:
If the goal is to show the Month that got the Highest count(DISTINCT Chart_No)

You only need to use TOP 1, you don't need sub select in the where clause!!!!  

This is the Correct way (better query plan and less IO) :

select top 1
Month(last_date) as [Mth],
Count(distinct Chart_No) as [ChartCount]
from Treatment
where last_date >= '2011-01-01'
and last_date < '2012-01-01'
Group by Month(last_date)
order by 
 ChartCount desc

Open in new window

0
Philippe DamervalSenior Analyst ProgrammerCommented:
Ashok:
I agree about awarding the points to acperkins. Although he was clearly disrespectful with us, he found the whole answer for you and that is what matters. I learned something about SQL server today which, despite my years of working with this DBMS, I had never encountered.

Anthony:
There are many other ways in which I, or any respectful person, could have gone about making the points you made, without insulting the others. If you think being wrong makes one look foolish... I can't really express how mistaken that is. More and more, what keeps people like you and me in this profession is not the extent to which we are always right, so much as our ability to detect when we are wrong, and relate to people who have the wrong idea so as to collectively arrive at the right idea. Projects and deliverables are not being cranked out by single experts anymore, they're being produced by teams. One of the primary qualities needed to be part of a team is respect, and you did not demonstrate that to Chris and I today.
For me the really interesting question here is how, after working with SQL server for over 10 years, and producing some pretty complex artifacts, I never knew this. I think I never encountered this SQL server-specific limitation of T-SQL because I always go for the holistic FROM clauses, putting the logic in the joins themselves. Coupled with my continued attempt at writing SQL that will execute in the same way in as many DBMS systems as possible that may not implement this limitation (I know informix and Oracle are two examples, I'm not sure about other databases), I can't really explain my missing what seems to be a ubiquitous characteristic of SQL server for all these years in any other way.
0
AshokSr. Software EngineerAuthor Commented:
Cboudroz,

Very clever.

I do not have many records in my test table so both SQL runs in 0.00 seconds.
But yours has only 6 rows with Query PLAN vs 13 rows.

Does less rows necessarily mean it is faster?

Thanks,
Ashok
0
AshokSr. Software EngineerAuthor Commented:
Cboudroz,

Using your solution with
SET STATISTICS IO ON

Table 'Treatment'. Scan count 1, logical reads 1
vs
Table 'Treatment'. Scan count 2, logical reads 2

so it looks like double speed (with your solution) especially when there are many records.

Thanks,
Ashok
0
CboudrozCommented:
Less IO mean faster... but I just realize a big difference whit my solution...

If you have two month with a egal number of MAX(Count(distinct Chart_No))

My query will only show one row, but the other solution will show both row.

...

!!
0
AshokSr. Software EngineerAuthor Commented:
Cboudroz,

This my sample data.

Chart_No      Proc_Code      Last_Date
12345           ABC1                  2011-01-22 00:00:00.000
12345           ABC2                  2011-08-22 00:00:00.000
12345           ABC3                  2011-03-22 00:00:00.000
12347           ABC2                  2011-04-22 00:00:00.000
12347           ABC3                  2011-05-22 00:00:00.000
12348           ABC1                  2011-08-22 00:00:00.000
12345           ABC1                  2011-01-22 00:00:00.000

In above I have August 2 times, but with damerval or your solution, I get exactly one record in result.

Mth      ChartCount
8      2

Remember, this is yearly data so there cannot be more than one same month.

Do you mean something else?

Thanks,
Ashok
0
CboudrozCommented:
if your having twice two date

like this:
Chart_No      Proc_Code      Last_Date
12345           ABC1                  2011-01-22 00:00:00.000
12345           ABC2                  2011-01-22 00:00:00.000
12345           ABC3                  2011-03-22 00:00:00.000
12347           ABC2                  2011-03-22 00:00:00.000
0
Philippe DamervalSenior Analyst ProgrammerCommented:
Quite right Choudroz!
Ashok, when two months share first place, do you need two rows, or just the most recent month? If the latter, Choudroz's expression can be tweaked a little by adding the month(last_date) value to the ORDER BY clause like this: ORDER BY ChartCount DESC, month(last_date) DESC.
If you need both rows, revert to the previous statement.
0
AshokSr. Software EngineerAuthor Commented:
I do have twice two date in my sample data.

2011-08-22 00:00:00.000

Above date is 2 times.

but I will get only 1 record (not 2).

Even with updated data like this

Chart_No      Proc_Code      Last_Date
12345           ABC1                  2011-01-22 00:00:00.000
12345           ABC2                  2011-08-22 00:00:00.000
12345           ABC3                  2011-03-22 00:00:00.000
12347           ABC2                  2011-09-15 00:00:00.000
12347           ABC3                  2011-09-15 00:00:00.000
12348           ABC1                  2011-08-22 00:00:00.000
12345           ABC1                  2011-01-22 00:00:00.000

I think it is because of DISTINCT, it will not return two.

Thanks,
Ashok
0
AshokSr. Software EngineerAuthor Commented:
I need ONE row.

Ashok
0
AshokSr. Software EngineerAuthor Commented:
damerval,

With your solution, and repeated DATE (twice same date with same Chart_No or different Chart_No(s)), I get
one record result which is what I am expecting.

Thanks,
Ashok
0
Anthony PerkinsCommented:
ashok111,

I feel like you deserve some points.
Not at all.  You arrived at the solution that worked for you with the help of Philippe.  You assigned the points correctly.

Philippe,
Point well made and I stand corrected.  I was frustrated at my apparent lack of communication skills at explaining something that I thought was easy to see.  I realize now that it was a misuderstanding that could have easily resolved with some screenshots to explain the syntax error.

Cboudroz,
As I alluded to previously I thought it was not the best solution, however my best attempts were using a CTE which of course is verboten in SQL Server 2000.  But I think you are on the right track.


All,
I am glad we have now got back what we do best here:  Share well thought out and proven technical ideas.  No one in their right mind is here for the points and we all have too much to learn to waste time on side-trips.

Best.
0
AshokSr. Software EngineerAuthor Commented:
I would like to assign points to
Cboudroz
for best solution.

Thanks
Ashok
0
CboudrozCommented:
Happy to help!!

thanks!
0
AshokSr. Software EngineerAuthor Commented:
Could you re-open this question?
0
Anthony PerkinsCommented:
ashok111,

As I stated before, I believe that damerval gave you a solution in good faith and you accepted it.  If you (or damerval for that matter) now feel the points awarded should change and you feel bound and determined about that, then you can object or post a message in Community Support to have the question opened.   Otherwise, I think we should leave it as is.
0
AshokSr. Software EngineerAuthor Commented:
Missing a character "b".
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.