Top10 from another view

  • Thread starter Thread starter Jose Perdigao
  • Start date Start date
J

Jose Perdigao

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 .
 
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
 
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.
 
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
 
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.
 
Back
Top