Re: Top10 from another view

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


"Jose Perdigao" <perdijc@xxxxxxxxxxx> wrote in message
news:uGEe7cWMGHA.500@xxxxxxxxxxxxxxxxxxxxxxx
Hi Sylvain,
Have you any ideia how to crete the query?

Thanks
josé perdigão


"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:uCG7Q4ZLGHA.3984@xxxxxxxxxxxxxxxxxxxxxxx
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 .

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


"Jose Perdigao" <perdijc@xxxxxxxxxxx> wrote in message
news:u2ks%23dYLGHA.3468@xxxxxxxxxxxxxxxxxxxxxxx
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








.

Relevant Pages

  • Re: Top10 from another view
    ... it may also require the use of a Cursor but as it seems that I ... MVP - Technologies Virtual-PC ... achieve it without using a primary key but it's much more complicated. ... SELECT dDate, Well ...
    (microsoft.public.access.adp.sqlserver)
  • Re: delete row query doesnt remove all row when run from access (ms sql)
    ... SET NOCOUNT ON ... DECLARE @dDate AS SMALLDATETIME ... When I run the query from Query Analyzer it does work perfectly. ...
    (microsoft.public.access.queries)
  • Re: Top10 from another view
    ... of correlated subqueries. ... In the case of a SP, you can also use a cursor ... without using a primary key but it's much more complicated. ... SELECT dDate, Well ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Top10 from another view
    ... if I have one day I sold 4 products the result shoud ... In the case of a SP, you can also use a cursor ... without using a primary key but it's much more complicated. ... SELECT dDate, Well ...
    (microsoft.public.access.adp.sqlserver)
  • Open store procedure from VBA
    ... I created a sp procedure with multi select statement. ... DECLARE @STR INT ... SELECT dDate, Area, Source, FctOil, FctWater, FctGross, FctGas ... If in the query analyzer I can open the right statement what is wrong? ...
    (microsoft.public.access.adp.sqlserver)