November 30, 2004 at 5:01 am
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.
November 30, 2004 at 5:23 am
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]
November 30, 2004 at 12:11 pm
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
November 30, 2004 at 1:51 pm
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.
Sean Wyatt
seanwyatt.com
November 30, 2004 at 1:58 pm
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
November 30, 2004 at 2:34 pm
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...
December 1, 2004 at 3:02 am
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
December 1, 2004 at 3:25 am
No, he's always like that.
December 1, 2004 at 3:31 am
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!?
December 1, 2004 at 3:33 am
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]
December 1, 2004 at 4:32 am
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!?
December 1, 2004 at 5:21 am
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]
December 1, 2004 at 8:07 am
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
December 1, 2004 at 8:31 am
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.
December 1, 2004 at 8:47 am
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]
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply