sub query and order by - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

sub query and order by

Author

Topic

thanksfor help
Posting Yak Master

106 Posts

Posted - 2007-12-12 : 16:30:09

Hi,

Here is my query

select MonthName
from (
SELECT distinct
MonthName,
[Year],
MonthID
FROM
tbl_FiscalTime
order by
[Year],
MonthID ) A

This query gives the error because order by in subquery, I do that so MonthName is sorted by year and monthid ( 1 for Jan, 2 for Feb).

Is there a way I can make at work.

MonthName Year Monthid Date
Jan 1990 1990 1 1/1/1990
Jan 1990 1990 1 1/2/1990
.
.
Feb 1990 1990 2 2/1/1990
Feb 1990 1990 2 2/2/1990
Mar 1990 1990 3
...
....
......

Dec 2008 2008 12 12/31/2008

Any suggestion will be appreciated

thanks in advance

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-12 : 16:36:04

Why are you using a derived table?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

thanksfor help
Posting Yak Master

106 Posts

Posted - 2007-12-12 : 16:40:41

I want only the Monthname returned from the query. In the following order Jan 1990,Feb 1990,Mar 1990,...................Dec 2008

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-12 : 16:43:09

ORDER BY items do not need to appear in the SELECT list, so you can do this:

SELECT MonthName
FROM tbl_FiscalTime
order by [Year], MonthID

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

dev2dev
Starting Member

48 Posts

Posted - 2007-12-13 : 11:31:40

thanksforhelp,

when ever you want to use order by clause in a subquery or in a outer most query in a view you must use TOP clause

try changing your query like this

select MonthName
from (
SELECT TOP 9999999 distinct
MonthName,
[Year],
MonthID
FROM
tbl_FiscalTime
order by
[Year],
MonthID ) A

using TOP in a query is risky, so
make sure the TOP 999999 to be as big as you don't miss any rows, i hope in your scenario you wont

thanksfor help
Posting Yak Master

106 Posts

Posted - 2007-12-13 : 12:12:10

Thanks for all replies, I used Dev2dev tip.

Tara, I wish I would do that, but I am getting an error saying the it should be in select list. If it requires any special setting, please let me know. I use sql server 2000.

thanks in adavnce.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-13 : 12:54:24

I'd have to see your entire code to know why you are getting that error.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-12-13 : 13:49:29

Ordering within a derived table makes no guarantees that your result will be ordered properly. You always need to ORDER at the outermost portion of your SELECT.

Also, it is because of using DISTINCT that you are getting the error about specifying all columns; you cannot logically distinctly select a set up columns and then order by different columns, it doesn't make sense.

see:

http://weblogs.sqlteam.com/jeffs/archive/2007/12/13/select-distinct-order-by-error.aspx

All you need to do is simply express the ordering outside of your derived table:

select MonthName
from (
SELECT distinct
MonthName,
[Year],
MonthID
FROM
tbl_FiscalTime
order by
[Year],
MonthID ) A order by [Year], [MonthID]

... or, as Tara said, you don't even need a derived table:

SELECT DISTINCT Year, MonthID, MonthName
FROM tbl_FiscalTime
order by [Year], MonthID

Make sure you really need DISTINCT. Never randomly add it to all of your SELECT statements, like many poor SQL programmers do.

- Jeff
http://weblogs.sqlteam.com/JeffS

thanksfor help
Posting Yak Master

106 Posts

Posted - 2007-12-13 : 15:20:18

Thank U all for the wonderfull suggestion.

I undetstood why I got the error,

select distinct monthname
from tbl_fiscaltime a
order by [year],[monthid]

Because the [year],[monthid] was not in the distinct list.

When I use the derived table it solved that issue. I am using this query to display monthname as prompt value in a reporting object. As the table is designed by each day, if I don't use distinct I will see 31 Jan 2007, 28 Feb 2007.......

Thanks again for all your time.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-12-13 : 15:25:01

Is there one row in your table per year/month? Then what's wrong with:

select monthname
from tbl_fiscaltime
where year=2006
order by monthid

(or any arbitrary year in your table that spans all 12 months)

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources