Rank in SQL

[jayeshp100]

does any one know how display the top 10 rows of query, ive tried to use the rank function but can get it work.select sum(d.price), e.Title, d.dvd_id from dvd_rental d, dvd_details e where ORDER_DATE between '4-NOV-04' and '04-NOV-05' and e.dvd_id = d.dvd_id group by d.dvd_id, e.title order by sum(d.price) desc /here's a query ive created, this displays the DVDs making the most money, how do i display just the top 10 rows by price.any ideas

[vijay]

Hi Try with this QRY:select top 10 sum(d.price), e.Title, d.dvd_id from dvd_rental d, dvd_details e where ORDER_DATE between '4-NOV-04' and '04-NOV-05' and e.dvd_id = d.dvd_id group by d.dvd_id, e.title order by sum(d.price) desc

[jayeshp100]

ive tried that way already, SQL does not understand what top 10 is, i think u only can use top in sql server and u have to use rank in sql plus so how.

[Kcarson]

Try this:

SELECT * FROM table LIMIT 10

It should work in MySQL and PostgreSQL

[jayeshp100]

Im using SQL plus not server

[Kcarson]

Im using SQL plus not server

<{POST_SNAPBACK}>

Sorry for not catching that. I do not have any experience or knowledge of SQL*Plus and after looking around, I cannot find anything that would give you the results you are looking for you.Hope you can find your answer or somebody else can help you.If you do find an answer please do share it with the rest of us so we can learn with you.

[Vamsy]

Hai,U want to display top 10, with my knowledge we can do it in 2 ways1. is using TOP keyword in sql (which u already tried)2. is using record set propertyby using option 2 we can restrict the record set to read n records. Just go thru record set properties, since i forget the syntax.Bye,Vamsy

[vijay]

Hi, Try with Limit keywordORI can tell it another way but its not properIn whatever order u want like asc ,descthen take first 10 rowSo easyyyyyyyy

[Kcarson]

Hi, Try with Limit keywordORI can tell it another way but its not properIn whatever  order u want like asc ,descthen take first 10 rowSo easyyyyyyyy

<{POST_SNAPBACK}>

The Limit Keyword and the Top Keywords have already been shown to him and are not allowed in SQL*Plus, and he is already performing the ordering (see original post). His question is how to get just the first 10 rows of his query using syntax that is allowed in SQL*Plus.

[Kcarson]

jayeshp100, I just saw an example that might be of help to you for this problem if you still need it. I have not had the opportunity to try it out myself, but I thought I would tell you anyways. Here is the example:

select *     from employee_tbl where rownum < 5; /* Only returns the top X rows */

Here is your query with it already inserted:

select sum(d.price), e.Title, d.dvd_idfrom dvd_rental d, dvd_details ewhere ORDER_DATE between '4-NOV-04' and '04-NOV-05' and e.dvd_id = d.dvd_id and rownum < 11group by d.dvd_id, e.titleorder by sum(d.price) desc

Let me know if it works

[vijay]

Hi, For that whatever U r using in that there is functionalityto get records in limit likewise if I am using CF in that Component having query has facilities of attribute like maxrow,startrow,endrowLikewise whatever u r using in that there is such facilitiesAnd Otherwise Acording To that According to ur Requirement u have to wright query and select only first 10 row with the help of view and all those thing.Best LuckVijay

[jayeshp100]

heres the way ive tried it.select sum(d.price), e.Title, d.dvd_id from dvd_rental d, dvd_details e where months_between(sysdate, order_date) < 13 and e.dvd_id = d.dvd_id and rownum <= 10 group by d.dvd_id, e.title order by sum(d.price) descheres the resultSUM(D.PRICE) TITLE DVD_I------------ -------------------- ----- 11.98 The_Lion_King 88755 9.99 Saw2 12563 9.39 Shrek 66504 8.99 Oceans_Eleven 97765 7.99 Die_Another_Day 76764 7.55 Independence_Day 34435 2.99 Tarzon 45564 2.99 Spiderman 56753 2.43 Jurrasic_Park 486549 rows selected.It looks correct but its not, the query for some reason takes out the top three rows which i know should be shown. It seems that the query is counting from bottom for some reason.

[Kcarson]

Okay, you have to be careful when using the rownum function. It numbers the rows before you do the order by, which is why you are missing the three rows. To fix this, all you need to do is place your original query (minus the rownum references) in a subquery, and in the outer query check for rownum. Query should look like this:

select *from ( select sum(d.price), e.Title, d.dvd_idfrom dvd_rental d, dvd_details ewhere months_between(sysdate, order_date) < 13 and e.dvd_id = d.dvd_idgroup by d.dvd_id, e.titleorder by sum(d.price) desc)where rownum <= 10

I have also heard that there is another way to do this, and that is

SELECT FIRST 10 sum(d.price), e.Title, d.dvd_idFROM dvd_rental d, dvd_details eWHERE months_between(sysdate, order_date) < 13 AND e.dvd_id = d.dvd_idGROUP BY d.dvd_id, e.titleORDER BY sum(d.price) DESC

Hopefully one of those two will work for you

[jayeshp100]

Thanks mate it works fine with the first query. thanks

[Kcarson]

Thanks mate it works fine with the first query. thanks

<{POST_SNAPBACK}>

Glad I could help

[ranw3schools]

suggestion: why did they not standardize the SQL statement use by any database vendor ?

[Kcarson]

suggestion:  why did they not standardize the SQL statement use by any database vendor ?

<{POST_SNAPBACK}>

There is a standard actually, its called ANSI-SQL, however most vendors want to add extra features or modify what is out there so that they can make it "easier" for the programmer. Here is a good description of it all (taken from http://www.vbip.com/books/1861001800/chapter_1800_02.asp)

"The computer industry (like most industries) both benefits and suffers from standards. We said that SQL is an open standard, not owned by a company, and the standard comes from ANSI. Therefore the SQL standard from ANSI is considered the "pure" SQL and called ANSI-SQL.Two problems emerge to sully this pureness. First is that every DBMS vendor wants to differentiate their DBMS products. So if you look at the feature set of each DBMS product you see that not only does the product support ANSI-SQL but it also offers extra features, enhancements or extensions that are available only from individual vendors. For example, most vendors offer a field type which auto- increments even though this is not described in the SQL standards. These additions to ANSI-SQL are generally proprietary and will not work if you try to use them on competitor's SQL products. Many of these features are powerful and robust, but since they vary from vendor to vendor, programmers should use them with caution. It is always safest to stick with pure SQL whenever possible; if you stray it should be with full knowledge that you are losing the portability of your statements (and perhaps even your data).Such enhancements are not all bad because these extensions are very useful. For example, ANSI-SQL does not contain an automatic way to assign a serial number to each new record but most DBMS sold today have added this feature. Since serial numbering is so common programmers are happy to have the enhancement. However, the method of implementation is not uniform, so code written to get the serial number from data in one DBMS may not work when used with another vendor's DBMS."

So it is standardized to some extent, and that is why so much of the SQL language will conform to different databases, however there are certain features which are unique to each DBMS and ranking is one of them.Hope that answers your question.

[jayeshp100]

i mate for the query that works i need to use it in asp so i can view it in table, i know how do this but it keeps coming up with the error belowORA-00933: SQL command not properly ended i have put the whole query on one line but i cant work out the problem

[Kcarson]

i mate for the query that works i need to use it in asp so i can view it in table, i know how do this but it keeps coming up with the error belowORA-00933: SQL command not properly ended i have put the whole query on one line but i cant work out the problem

<{POST_SNAPBACK}>

I think Oracle might require a semi-colon at the end of their statements....just a guess though.