Order by problem

Question

Post reply

Order by problem

Srinivas-120709

SSC Veteran

Points: 273
More actions
November 30, 2004 at 5:01 am

#361693

When I say this :
CREATE VIEW A AS
SELECT NAME, NO,ADDRESS ,DESCRIPTION
FROM TEST WHERE IS_TRUE='F' ORDER BY NAME
I get the message as below :
The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.
If i use Top n, I have to specify the n , I need the entire listing with order by name
Please tell me an alternative solution for this.

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

create view dbo.foobar

as

select top 100 percent * from authors order by au_lname

go

select * from dbo.foobar

drop view dbo.foobar

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

kknudson SSCarpal Tunnel Points: 4986 More actions · Answer 2

Having a bad day Joe ??

While your statements are correct, take it easy on people. Not everyone has the knowledge and understanding of IT that some of us do.

At least this person is asking for help, sugggest, help out but ....

I have a customer here, he is an ex DBA, that I would be happy if he did something like that.

He just plopped down a system to run on SQL that is little more than an 80s style batch process. Including a crude match merge using Dynamic Update cursors and a naming convention that looks just like what I used 25 years ago on the mainframe.

Runs for hours.

Even our request that he truncate the 12 million run table before he drops the clustered index was met by extreme NO. We did it anyway.

A join drops the hours to 10 or 20 minutes, but he won't hear of it.

KlK

Sean Wyatt SSChasing Mays Points: 638 More actions · Answer 3

I think for some reason SQL requires a different thought pattern than standard programming. Much of the reading I have done instructs you to think of SQL as just another language but I disagree. An underlying familiarity with the consequences of relational data and a "feel" for SQL seems neccessary before success can arrive.

Joe may be right in that switching from sequential to relational logic is the key.

Kindest Regards,

Sean Wyatt
seanwyatt.com

kknudson SSCarpal Tunnel Points: 4986 More actions · Answer 4

Unfortunately I see it as a big problem in our profession. And I unfortunately use the term profession loosely now a days.

I am a professional, I take pride in what I do. I still believe that a good developer, (and several other IT disiplines) are somewhat an art. Something that cannot be taught, even beaten in with a stick. I also have conquered many disiplines, I am as good a coder as you'll find, same with design, developer, dba, networker (ok I'm good at networking, above average), PCs, servers, mainframes.... And I'll match my debugging and problem solving against almost anyone.

Too many people around us got into IT, with a degree, figuring they had a job for life. But are so bad at it it takes 3 or 4 to do what we can do, so now companies outsource to get it done cheaper, but not better. Or sometimes did the work so bad it takes 3 people to maintain it, when done right it should have taken almost none.

If skilled and dedicated professionals had done the job, there would be a third of the IT people today, but everybody would have a job.

Note, part of the problem too is the managers that would hire anybody, because more bodies do more work (:-}), and the more people working for me, the more important I am :-}

Just my view and opinion, but I've been doing this for 35+ years, still love it and spend WAY too much time working or playing (not games, my term for learning) on computers.

Final statement

"Too many IT Workers, not enough IT Professionals"

Thanks I needed that.

KlK

lalibert16 SSC Eights! Points: 928 More actions · Answer 5

I still think that there is no need to give shit to this person. If you don't want to answer newbies question, just don't. Nobody force you to. I don't say I disagree with what you said, but I think this forum is useful for everyone and there is no mention "EXPERTS ONLY" anywhere in those pages.

I just think everyone has to start from the beginning, you can't become a pro like that...

RemyDeAlmeida Newbie Points: 6 More actions · Answer 6

Hello Joe,

I am not sure why you jumped to the conclusion that the newbie is a programmer. He calls himself newbie which means he is most possibly trying to learn this new subject. Just the way we tried to learn alphabets ABCD.... before we started to to talk...

May be apart from reading books he using this forum to learn more and take on the world like many of us.

I think there is no need to be nasty while sharing your knowledge. If you think that question is that too dump you can choose not to answer and let someone else do the teaching more professionally.

Cheers,

Michel

AKM SSCommitted Points: 1538 More actions · Answer 7

AKM

SSCommitted

Points: 1538

December 1, 2004 at 3:25 am

#532020

No, he's always like that.

vinny_1973 SSCommitted Points: 1782 More actions · Answer 8

Ignore Joe ... to be fair, the tone he's taken here is actually quite mild in comparison to what he puts in the MSDN newsgroups! what i do always find entertaining is that he NEVER actually offers any advice ... he just does what he has done here and slags ppl off!?

Has this strange obsession about DB's becomeing "file systems" as well for some reason!?

Frank Kalis SSC Guru Points: 111183 More actions · Answer 9

what i do always find entertaining is that he NEVER actually offers any advice

Just out of curiosity, do you actually read what he writes?

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

vinny_1973 SSCommitted Points: 1782 More actions · Answer 10

only for fun ... and occasionally add a reply to his postings (C&P'ed from a notepad file now!! ) saying "if you dont have anything constructive to say, dont say anything at all!!"

Basically, he's a very narrow minded puritan as far as DB's and T-SQL are concerned and doesn't seem to realise that there ARE other ways of doing things!?

Frank Kalis SSC Guru Points: 111183 More actions · Answer 11

That's your opinion and I respect it. Others might think different.

However, I also read his postings. I don't agree with him on many points, but actually there is no need to agree with him for life to go on, right?

After all, the whole discussions here recently are to me nothing but much ado about nothing. We spent too much attention on this, instead of focusing on what we are here for. Things are pretty much self-regulating here. And there is absolutely no need, wish, or desire to raise a witch-hunt after each and every damn single post by him. If you don't agree, ignore him or answer. But keep focused on the topic and use a proper language.

So, this will be my last post to this "Joe Celko's forum behaviour" topic.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

kknudson SSCarpal Tunnel Points: 4986 More actions · Answer 12

I started this, but didn't mean it to be a "Joe flame" thread.

So I'll close it here.

And following some other comments, after all my years, after all the books, after all the playtime, I find the information up here invaluable. I have solved many problems, no one knows all the answers. Learned many things, and will continue to learn.

Maybe thats the key, people need to be willing to learn constantly. If nothing else our industry changes so fast, you get left behind if you don't or won't.

Let's follow Franks comments and post to help, otherwise don't.

KlK

PhilPacha Hall of Fame Points: 3645 More actions · Answer 13

A correction to logic posted earlier...

create view dbo.foobar

as

select top 100 percent WITH TIES * from authors order by au_lname

go

select * from dbo.foobar

drop view dbo.foobar

Read Books Online (BOL) for "gotchas". Without the "WITH TIES" option, the resultset can exclude rows which match the last "ORDER BY" value; only the "first" row with that value will be returned.

from BOL...

WITH TIES

Specifies that additional rows be returned from the base result set with the same value in the ORDER BY columns appearing as the last of the TOP n (PERCENT) rows. TOP ...WITH TIES can only be specified if an ORDER BY clause is specified.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 14

Hm, I'm not sure if I follow you here. Consider this:

SET NOCOUNT ON

DECLARE @TTT TABLE

(

THE_ID INT NOT NULL

, Name varchar(50)

)

INSERT INTO @TTT VALUES(1,'A');

INSERT INTO @TTT VALUES(2,'BB');

INSERT INTO @TTT VALUES(2,'B');

INSERT INTO @TTT VALUES(3,'C');

INSERT INTO @TTT VALUES(3,'D');

INSERT INTO @TTT VALUES(3,'DD');

INSERT INTO @TTT VALUES(3,'DDD');

INSERT INTO @TTT VALUES(3,'DDDD');

INSERT INTO @TTT VALUES(4,'E');

INSERT INTO @TTT VALUES(5,'F');

INSERT INTO @TTT VALUES(6,'G');

SET NOCOUNT OFF

SELECT TOP 4

THE_ID

FROM

@TTT

ORDER BY

1;

SELECT TOP 4 WITH TIES

THE_ID

FROM

@TTT

ORDER BY

1;

SELECT DISTINCT TOP 4

THE_ID

FROM

@TTT

ORDER BY

1;

SELECT TOP 100 PERCENT

THE_ID

FROM

@TTT

ORDER BY

1;

SELECT TOP 100 PERCENT WITH TIES

THE_ID

FROM

@TTT

ORDER BY

1;

Am I missing something or has WITH TIES no effect in the last query since I SELECT TOP 100 PERCENT anyway.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]