Order by problem

  • 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.

  • 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]

  • 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

  • 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

  • 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

  • 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...

  • 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

  • No, he's always like that.

  • 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!?

     

  • 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]

  • 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!?

  • 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]

  • 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

  • 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.

  • 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