Top10 from another view

[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

 

[Sylvain Lafontaine]

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 .

 

[Jose Perdigao]

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

 

[Jose Perdigao]

Hi Sylvain,
Have you any ideia how to crete the query?

Thanks
josé perdigão

 

[Sylvain Lafontaine]

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.

 

[Jose Perdigao]

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

 

[Sylvain Lafontaine]

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.