Re: Ranking query

Thanks again for the information. I did some experiments, and saw how the various queries handle the data. One thing I'm certainly seeing is that there may be several options for solving any problem. The observation about the subquery running for each record is something to keep in mind. I expect that limiting the recordset on which the subquery operates is the way to keep that from getting bogged down when a subquery is needed. In any case, I expect that using a named query (as opposed to its SQL) will be OK.

I see that a non equi-join is something such as you demonstrated in your last example (and elsewhere) in which the join is on something other than Equal To. I'll have to keep an eye out for that. I can imagine places where it will be useful.

Thanks again for your input and suggestions.

"Dale Fye" <dale.fye@xxxxxxxxxx> wrote in message news:B7C7A61D-2C69-4DFD-8F2B-BE1E2555AC1F@xxxxxxxxxxxxxxxx
Based on your comments, and my reanalysis of your data, I should have joined
on UnitID and DetailID (instead of RepairDate), something like:

ON T2.UnitID = T1.UnitID
AND T2.UnitID <= T1.UnitID

Let me give you an example. Suppose you have a table that contains the
values 1-10, but what you want is a qruery to list for each item in that
table, all of the numbers less than or equal to it. Now you could do this as:

Select T1.intNumber, T2.intNumber
FROM yourTable as T1, yourTable as T2
WHERE T2.IntNumber <= T1.intNumber
ORDER BY T1.IntNumber, T2.intNumber

You could also do this as:

Select T1.intNumber, T2.intNumber
FROM yourTable as T1
LEFT JOIN yourTable as T2
ON T2.intNumber <= T2.intNumber
ORDER BY T1.intNumber, T2.intNumber

Now, if you wanted a count of the number of numbers in your table, that were
less than or equal to a number, you might use:

SELECT T1.intNumber, (SELECT Count(*)
FROM yourTable
WHERE intNumber <= T1.intNumber) as
FreqCount
FROM yourTable as T1

But with this method, you have to run the subquery for each record in T1.
Another way to do this would be:

SELECT T1.intNumber, Count(T2.intNumber) as FreqCount
FROM yourTable as T1
INNER JOIN yourTable as T2
ON T2.intNumber <= T1.intNumber
Group BY T1.intNumber

In my admittedly limited testing, I've found that this latter query
generally runs quicker.

HTH
Dale
--
HTH
Dale

Don''''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



"BruceM" wrote:

Thanks for the suggestion. As it turned out it did not do what I needed in
that the order became 1, 3, 3, 1. However, it got me going in a different
direction than I had tried before, and I ended up with:

SELECT Count(*) AS ListOrder, I1.UnitID, I1.RepairDate, I1.DetailID
FROM qryRepairs AS I1
INNER JOIN qryRepairs AS I2
ON I2.UnitID = I1.UnitID
WHERE (((I2.DetailID)<= I1.DetailID)
AND ((I2.RepairDate)<= I1.RepairDate))
OR (((I2.RepairDate)<=I1.RepairDate)
AND ((I2.RepairDate)<>I1.RepairDate))
GROUP BY I1.UnitID, I1.RepairDate, I1.DetailID
ORDER BY I1.UnitID, Count(*);

This gives me the desired result, and incidentally it can be viewed in
design view. I'm not exactly sure what you mean by non equi-join, but it is
certainly true that it cannot be viewed in design view. I see that the
inner join in your suggestion has an AND component, and that it is
non-equal, so I expect that's what you mean, but I have to admit I don't see
what it does.

"Dale Fye" <dale.fye@xxxxxxxxxx> wrote in message
news:8F117B5E-C14C-433B-965D-62EAAB68C305@xxxxxxxxxxxxxxxx
> Bruce,
>
> Try this. This uses a non equi-join, so you can only complete it in > the
> SQL
> view. I generally set it up in the design view with equi-joins (which
> should
> result in all the ListOrders = 1. Then, jump over to the SQL view and
> modify
> the second join, which should get you what you are looking for.
>
> SELECT Count(I2.*) as ListOrder, I1.UnitID, I1.RepairDate, I1.DetailID
> FROM qryRepairs as I1
> INNER JOIN qryReparis as I2
> ON I2.UnitID = I1.UnitID
> AND I2.RepairDate <= I1.RepairDate
> GROUP BY I1.UnitID, I1.RepairDate, I1.DetailID
> ORDER BY I1.UnitID, Count(I2.*)
>
> HTH
> Dale
> -- > Don''t forget to rate the post if it was helpful!
>
> email address is invalid
> Please reply to newsgroup only.
>
>
>
> "BruceM" wrote:
>
>> I have a query that assigns row numbers to the recordset returned by
>> another
>> query:
>>
>> SELECT Insp1.RepairDate, Insp1.DetailID, Insp1.UnitID
>>
>> (SELECT Count(*)
>>
>> FROM qryRepairs
>>
>> AS Insp2
>>
>> WHERE Insp2.RepairDate <= Insp1.RepairDate
>>
>> AND (Insp2.DetailID <= Insp1.DetailID
>>
>> OR Insp2.RepairDate <> Insp1.RepairDate))
>>
>> AS ListOrder
>>
>> FROM qryRepairs AS Insp1;
>>
>>
>>
>> This works up to a point, which is that I want the count to start over
>> when
>> there is a new I_UnitID value. This is what I get:
>>
>>
>>
>> ListOrder UnitID RepairDate DetailID
>>
>> 1 29 4/14/2008 42
>>
>> 2 29 4/16/2008 18
>>
>> 3 29 4/16/2008 19
>>
>> 4 30 4/16/2008 39
>>
>> 5 30 4/16/2008 40
>>
>>
>>
>> However, I want ListOrder to start over at 1 with UnitID 30. This is >> an
>> abridged version of the SQL, but the idea is that this shows a repair
>> history for an individual piece of equipment (UnitID). One table >> lists
>> types of equipment (e.g. clamping fixture), and a related table lists
>> individual equipment items (Fixture 1, Fixture 2, etc.). These are
>> brought
>> together, along with repair records for individual equipment items, >> into
>> qryRepairs. At the form level, the user navigates from one individual
>> equipment record to another. At each record a subform displays the
>> repairs
>> that have been performed. For Fixture 1 (UnitID 29) there are three
>> items
>> on the list, numberd 1, 2, and 3. At the next record (for Fixture 2)
>> there
>> are two items on the list. They should be numbered 1 and 2, not 4 and >> 5.
>>
>>
>>
>> One way to do this, I suppose, is to load the SQL at run time (in the
>> main
>> form's Current event?) as I move to each record, so that it includes >> only
>> one UnitID. I don't know if there is a performance hit by doing it >> this
>> way
>> (there could be tens of thousands of records eventually). I have not
>> been
>> able to discover a way to restart the numbering using SQL.
>>
>>
>>
>> A related question is whether there are any considerations one way or >> the
>> other to using a named query in another query. I could replace the >> name
>> of
>> the query with the query's SQL in the example above, if there is a >> reason
>> for doing so.
>>
>>
>>
>>



.

Relevant Pages

  • Re: SQl Data Provider Performance Issues
    ... selects with a single inner join perforn OK. ... When I run the query from SQL ... Analyzer, SQL Profiler shows a duration of 30. ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: SQl Data Provider Performance Issues
    ... selects with a single inner join perforn OK. ... When I run the query from SQL Query ... Analyzer, SQL Profiler shows a duration of 30. ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: SQl Data Provider Performance Issues
    ... Can you provide a complete sample app ... selects with a single inner join perforn OK. ... When I run the query from SQL ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: SQl Data Provider Performance Issues
    ... selects with a single inner join perforn OK. ... When I run the query from SQL ... Analyzer, SQL Profiler shows a duration of 30. ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Ranking query
    ... I expect that using a named query (as opposed to its SQL) will be OK. ... INNER JOIN qryRepairs AS I2 ... ListOrder UnitID RepairDate DetailID ...
    (microsoft.public.access.queries)