 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / SQL Server / ADP / February 2006Top10 from another view
I would like to create a view based in a sp or another view like the following example: SELECT dDate, Well FROM dbo.J1_WTzDL WHERE (dDate = iDate()) We have results as below: dDate Well 10/12/2005 ALB - 4H 10/12/2005 BAG - 4L 10/12/2005 CHO - 1 10/12/2005 EST-A-2 10/12/2005 EST-B-1 Now, I would like to create a view to show the top 10 for each well from the results above and dDate<=iDate() The following view is not complete I don't know how I can report the top 10 for each well. SELECT TOP 10 dDate, Well, TestGood, TestOil, TestWater FROM dbo.J1_WTzDL WHERE (dDate <= dbo.iDate()) ORDER BY dDate DESC How can I create this view? Thanks josé perdigão Creating such a query is called Ranking and is usually done with the use of correlated subqueries. In the case of a SP, you can also use a cursor and a temporary or a local table to achieve it. For the View, it is also a requirement that each record has a primary key (you can achieve it without using a primary key but it's much more complicated. Here a possible solution that I didn't test. It's based on the fact that the primary column is the ID field. Notice the use of the alias w1 and w2: SELECT w1.dDate, w1.Well, w1.TestGood, w1.TestOil, w1.TestWater FROM dbo.J1_WTzDL w1 Where w1.Id in (select top 10 w2.Id from dbo.J1_WTzDL w2 where (w2.Well = w1.Well) and (w2.dDate <= dbo.iDate()) order by dDate desc) ORDER BY w1.Well ASC, w1.dDate DESC Also, using a temporary or local table with a cursor will probably be a much more efficient design. For other ideas on ranking issues; see http://www.aspfaq.com/show.asp?id=2427 .  SignatureSylvain Lafontaine, ing. MVP - Technologies Virtual-PC E-mail: http://cerbermail.com/?QugbLEWINF >I would like to create a view based in a sp or another view like the >following example: [quoted text clipped - 44 lines] > > josé perdigão Hi Silvain, I tested your script and the results is 10 rows. What I would like is, if I have one day I sold 4 products the result shoud be 40 rows. I mean, the query shoud be show the last 10 sells for each product (10 rows for each product.) Could you help me? Thanks, José Perdigão > Creating such a query is called Ranking and is usually done with the use > of correlated subqueries. In the case of a SP, you can also use a cursor [quoted text clipped - 65 lines] >> >> josé perdigão Hi Sylvain, Have you any ideia how to crete the query? Thanks josé perdigão > Creating such a query is called Ranking and is usually done with the use > of correlated subqueries. In the case of a SP, you can also use a cursor [quoted text clipped - 65 lines] >> >> josé perdigão Nope, I have no other idea. This query is the best thing that I could make from what I've understood of your description of the problem and the fact that I cannot test it because I don't have access to your design and data. I suggest that you first try by using a SP and a temporary table (or a local variable table) instead of a View. With the use of a temporary table, you can easily divide your problem into each of its steps. Sometimes, it may also require the use of a Cursor but as it seems that I don't really understand your problem, I cannot tell you more on this. Often, you can achieve the same result with correlated subqueries but this require a lot more of experience because their understanding is much more complicated. SignatureSylvain Lafontaine, ing. MVP - Technologies Virtual-PC E-mail: http://cerbermail.com/?QugbLEWINF > Hi Sylvain, > Have you any ideia how to crete the query? [quoted text clipped - 71 lines] >>> >>> josé perdigão Hi Sylvain, How can I make a local variable table or temporary table? In mdb I know how can I do, but in ADP I don't have idea. When you say local variable table, it means, the table is stored in front end? Thanks José Perdigão > Nope, I have no other idea. This query is the best thing that I could > make from what I've understood of your description of the problem and the [quoted text clipped - 86 lines] >>>> >>>> josé perdigão By local variable tables (or mayb local table variables, not sure of the translation) and temporary tables, I mean directly on the SQL-Server, inside a Stored Procedure (SP). If you want to use SQL-Server as the backend, then it's a good idea to lean T-SQL, SP and UDF (User Defined Functions). With ADP, it's a good idea to replace a lot of VBA code to T-SQL. Unlike MDB, ADP doesn't have local table; all tables are on the SQL-Server side. SignatureSylvain Lafontaine, ing. MVP - Technologies Virtual-PC E-mail: http://cerbermail.com/?QugbLEWINF > Hi Sylvain, > How can I make a local variable table or temporary table? In mdb I know [quoted text clipped - 95 lines] >>>>> >>>>> josé perdigão |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.