Wrong QOD 16-12-2003

Question

Post reply

Wrong QOD 16-12-2003

k_vasanthkumar

Old Hand

Points: 349
More actions
December 16, 2003 at 12:14 am

#161003

Hi All,
The QOD for 16-12-2003 was
CREATE VIEW v_selectarticle
AS
SELECT TOP 100 PERCENT article_id, headline FROM ARTICLES
ORDER BY articleid desc
as u can c the quesion is itself wrong because how can u order by articleid field neither exist in the table nor alias name is given for the field article_id.
Regards,
Vasanth

Viewing 12 posts - 1 through 11 (of 11 total)

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

Johan Bijnens SSC Guru Points: 135307 More actions · Answer 1

correct.

Should be :

CREATE VIEW v_selectarticle

AS

SELECT TOP 100 PERCENT article_id, headline FROM ARTICLES

ORDER BY article_id desc

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Tom Gjøs SSC Rookie Points: 42 More actions · Answer 2

Why is not alternative 2 correct?

CREATE VIEW v_selectarticle

AS

SELECT article_id, headline FROM ARTICLES

ORDER BY articleid desc

Is it any difference to 100 Percent?

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

Mainly because the view is syntactically not correct.

You're referencing a column articleid in the ORDER BY that seem not to exist in the underlying table.

Frank

http://www.insidesql.de

http://www.familienzirkus.de

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

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

Apart from this lesser flaw (IMHO) try to run the given alternatives from QA. Only one should create the view.

Frank

http://www.insidesql.de

http://www.familienzirkus.de

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

Tom Gjøs SSC Rookie Points: 42 More actions · Answer 5

It was the same syntax error for the "correct" answer. My question wa:

Is there any difference to a view if one add "top 100 percent" ?

CREATE VIEW v_selectarticle

AS

SELECT TOP 100 PERCENT article_id, headline FROM ARTICLES

ORDER BY articleid desc

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

The results would be the same. However, try to create a view without specifying TOP 100 PERCENT.

Frank

http://www.insidesql.de

http://www.familienzirkus.de

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

Cray Newbie Points: 9 More actions · Answer 7

With those syntax errors, none of the answers could work today.

However, I do not understand why :

select article_id, headline

from articles

order by article_id desc

does not work

Tom Gjøs SSC Rookie Points: 42 More actions · Answer 8

This is why:

[Server: Msg 1033, Level 15, State 1, Procedure v_selectarticle, Line 5

The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.

*Prashant Thakwani SSCommitted Points: 1827 More actions · Answer 9

Because, U cannot specify the ORDER BY Clause, until the TOP clause is also applied in the SELECT Clause. Remeber this, Always.

Prashant Thakwani

thakwani@rediffmail.com

Prashant Thakwanithakwani_prashant@yahoo.co.in

WebPhil Ten Centuries Points: 1303 More actions · Answer 10

Oops, I posted about the wrong field name in the 15th thread

Oh well, at least they've got it sorted on the site

Greg Charles SSC-Forever Points: 45403 More actions · Answer 11

Once again, the typo (articleid) in the email is corrected (article_id) on the web page. This has happened enough times that I always go to the web page to read the QOD now. I don't even look at it in the email.

Greg