Re: Why doesn't this work?

> 1- views do not support order bys, never have

A flaw in YOUR sacred cow.

> 2- group by is NOT equal to order by and cannot be made so in general

if, by 'in general' you mean always, OK - but if by 'in general' you mean frequently then I disagree

> 3- group by doesn't even have to sort by the columns you specify (see below)
>
> > The flaw is that Oracle doesn't allow ORDER BY in views.
>
> SQL doesn't allow for it. It doesn't make sense to order by views.

It does to your users. And you are still forgetting that the users are your customers and your customers hold the life-line to your business in their hands. THIS is what I mean by 'Philosophical Arrogance'. This is the same kind of thing that allowed Japan to take the transistor radio market away from US.

> You cannot create an ORDERED table

and you should be able to.

> In many cases, views are built on views and people query from more then one
> view. What does it mean to have an order by on one of those then? This is like
> people who ask to insert "sorted" data into a table so it comes back out sorted
> -- you can't and it won't.

You still don't get it do you. If your customer wants something, and you refuse to provide it long enough, then someone will come along and provide it and take your customer away from you.

> if it ain't broke.

I see the arrogance runs deep. You sound like the 'be all end all' corporation on the tv show 'Dinosaurs' - - - the 'We Say So' corporation. Sheesh! and the government is going against Microsoft instead of you guys! You keep forgetting - you are in a competitive business, and what the customer wants and needs is the definition of what is not broke, not yours.

> No you cannot make group by act like order by (what does a primary key have to
> do with anything anyway? If I don't put the primary key into the view?). You
> gave a general purpose solution that purported that group by orders, so all you
> need to do is group by. Your example used derived columns and one column from
> the table (primary key not included).

If I always put the primary key in the group by then I will not 'loose' rows. See related posts under subject 'calling all sql gurus'. This line of posting was a follow-on to that one.

> Its 100% bad advice to give as the performance implications are terrible as
> well.

I still haven't seen your solution to this persons problem. You act like the doctor when his patient comes to him and says 'Doc, it hurts when I do this' and the Doc says 'well, don't do that'. This is what I mean by arrogance. Maybe my solution wont work all the time. I will have to experiment with your example problem when I can get to work and try it out. However, I didn't leave him in a lurch and not reply. Nor did I (as others have done) say that it cant be done, therefore it shouldn't be done.

I did try out my solution to his original post 'calling all SQL gurus' on my machine at work, and it did work just as I said.  

> When I see something wrong -- I follow up. You gave an answer (as a followup to
> my answer) that was plain BAD ADVICE. Sorry you disagree but this is what the
> groups are about.

When PMG first posted his problem (under the subject of 'Calling all SQL gurus' I notice that you didn't follow your supposedly cherished principle of trying to help, instead of saying it cant be done. Received on Sun Dec 13 1998 - 14:37:30 CST