Re: Picking only first record from records - QUITE BIG and COMPLEX

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

From: Richard (Richard_at_discussions.microsoft.com)
Date: 08/19/04 

Date: Thu, 19 Aug 2004 03:11:02 -0700

Thanks a lot. It has moved the speed little bit up.

"Brian Camire" wrote:

> If your table has a single-field primary key or unique index (say, on a
> field named "Your Primary Key Field"), you might try something like:
>
> SELECT
> [Your Table].*
> FROM
> [Your Table]
> WHERE
> [Your Table].[Your Primary Key Field] =
> (SELECT TOP 1
> [Self].[Your Primary Key Field]
> FROM
> [Your Table] AS [Self]
> WHERE
> [Self].[IDN] = [Your Table].[IDN]
> AND
> [Self].[Shift] = [Your Table].[Shift]
> ORDER BY
> [Self].[Machine] DESC,
> [Self].[Date] DESC)
>
> Otherwise, things get a little more complicated. You might try using three
> queries:
>
> 1. One (say, named "Query1") that finds the Machine with the highest number
> for each IDN and Shift whose SQL looks something like this:
>
> SELECT
> [Your Table].[IDN],
> [Your Table].[Shift],
> Max([Your Table].[Machine]) AS [Machine]
> FROM
> [Your Table]
> GROUP BY
> [Your Table].[IDN],
> [Your Table].[Shift]
>
> 2. A second query (say, named "Query2") that finds the latest Date for the
> Machines identified by Query1 whose SQL looks something like this:
>
> SELECT
> [Your Table].[IDN],
> [Your Table].[Shift],
> [Your Table].[Machine],
> Max([Your Table].[Date]) AS [Date]
> FROM
> [Your Table]
> INNER JOIN
> [Query1]
> ON
> [Your Table].[IDN] = [Query1].[IDN]
> AND
> [Your Table].[Shift] = [Query1].[Shift]
> AND
> [Your Table].[Machine] = [Query1].[Machine]
> GROUP BY
> [Your Table].[IDN],
> [Your Table].[Shift],
> [Your Table].[Machine]
>
> 3. If necessary, a third query that brings in the other fields from Your
> Table for the records identified by Query1. The SQL might look something
> like this:
>
> SELECT
> [Your Table].*
> FROM
> [Your Table]
> INNER JOIN
> [Query2]
> ON
> [Your Table].[IDN] = [Query2].[IDN]
> AND
> [Your Table].[Shift] = [Query2].[Shift]
> AND
> [Your Table].[Machine] = [Query2].[Machine]
> AND
> [Your Table].[Date] = [Query2].[Date]
>
> This third query would have to assume that the combination of IDN, Shift,
> Machine, and Date was unique. Post back if you cannot make this assumption.
>
> As for speeding things up:
>
> 1. Are the IDN, Shift, Machine and Date fields indexed?
>
> 2. As opposed to using a SELECT TOP subquery, you might try the multi-step
> GROUP BY and then INNER JOIN approach above. It might or might not speed
> things up.
>
> Hope this helps.
>
> "Richard" <Richard@discussions.microsoft.com> wrote in message
> news:616F3FEC-CD83-4491-A14B-4AF88A919FFA@microsoft.com...
> > If I have the same query;
> >
> > AAA:
> > IDN Shift Date Machine
> >
> > but I want to pick only that record for a given IDN and SHIFT, where the
> > MACHINE has the highest number, if they are same than it will take the
> latest
> > date.
> >
> > Also the query AAA has 8000 records thus calculating the [self] query
> takes
> > few minutes, cca. 5.
> >
> > Could you help still?
> >
> > Thanks.
> > Richard
> >
> > "Brian Camire" wrote:
> >
> > > The query I posted will already pick the "lowest" machine in the event
> that
> > > there are more than one machines that share the earliest date for a
> given
> > > IDN and Shift. How is this different from what you want?
> > >
> > > Please post the SQL that you are having the syntax trouble with.
> > >
> > > "Richard" <richard@discussions.microsoft.com> wrote in message
> > > news:917918E5-26B2-45AE-8F3F-768B6B7A91E7@microsoft.com...
> > > > Dear Brian, first of all thank you for your reply...
> > > >
> > > > This works as I needed. If I understand your solution okay; you used
> > > Select
> > > > Top 1 record.
> > > >
> > > > I have another question;
> > > >
> > > > If I want only one record out of few similar where I want to pick the
> one
> > > > with machine number highest / lowest, I understand I should use ORDER
> BY
> > > .....
> > > > desc, but I tried and it does not work - says bad sytax.
> > > >
> > > > Could you provide me with a hint how to do it?
> > > >
> > > > Thanks again.
> > > >
> > > > PS. This is not a table but a QUERY out of a table. Thus no record is
> > > unique.
> > > >
> > > > "Brian Camire" wrote:
> > > >
> > > > > You might try a query whose SQL looks something like this:
> > > > >
> > > > > SELECT
> > > > > [Your Table].*
> > > > > FROM
> > > > > [Your Table]
> > > > > WHERE
> > > > > [Your Table].[Machine] IN
> > > > > (SELECT TOP 1
> > > > > [Self].[Machine]
> > > > > FROM
> > > > > [Your Table] AS [Self]
> > > > > WHERE
> > > > > [Self].[IDN] = [Your Table].[IDN]
> > > > > AND
> > > > > [Self].[Shift] = [Your Table].[Shift]
> > > > > ORDER BY
> > > > > [Self].[Date],
> > > > > [Self].[Machine])
> > > > >
> > > > > If the combination of IDN, Shift, Date, and Machine is not unique,
> this
> > > > > query may return more than one record for a given IDN and Shift.
> Post
> > > back
> > > > > if this is a problem, and describe how you would like to break the
> ties.
> > > > >
> > > > > "Richard" <Richard@discussions.microsoft.com> wrote in message
> > > > > news:C9F9B2FD-3973-494B-9C13-DD84561A6C0B@microsoft.com...
> > > > > > Dear all,
> > > > > >
> > > > > > I have following table
> > > > > >
> > > > > > IDN Shift Date Machine Fault1 Fault2 Fault3 etc.
> > > > > >
> > > > > > I want to make a query which would
> > > > > > group by IDN
> > > > > > group by Shift
> > > > > >
> > > > > > AND
> > > > > >
> > > > > > pick only one date (e.g. the oldest date) of "Date" and pick only
> > > > > "Machine"
> > > > > > recoreded in this date.
> > > > > > If the "date" is unique per same IDN but per same IDN the machine
> is
> > > > > > different, this date will be picked and the "Machine" from only
> one
> > > record
> > > > > > (eg. the first record) will be picked.
> > > > > >
> > > > > > An example:
> > > > > >
> > > > > > IDN Shift Date Machine Fault1 Fault2
> > > > > > 232 A 1/1 5 3 4
> > > > > > 232 A 1/2 4 0 1
> > > > > > 232 B 1/1 4 1 0
> > > > > > 232 B 1/1 6 0 0
> > > > > > 233 A 1/1 2 0 4
> > > > > >
> > > > > > Query output:
> > > > > >
> > > > > > IDN Shift Date Machine
> > > > > > 232 A 1/1 5
> > > > > > 232 B 1/1 4
> > > > > > 233 A 1/1 2
> > > > > >
> > > > > > I have quite a big problem because working on it for few hours.
> > > > > > If anybody can help, would be perfect...
> > > > > >
> > > > > > Thanks in advance,
> > > > > >
> > > > > > Richard
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>

Relevant Pages

  • Re: SQL parameter query not returning values
    ... I changed mt SQL to the following and it seems to be working well. ... FROM Shift INNER JOIN (((Department INNER JOIN Production ON ... ' Define the parameters clause. ...
    (comp.databases.ms-access)
  • Re: Picking only first record from records - QUITE BIG and COMPLEX
    ... field named "Your Primary Key Field"), ... This third query would have to assume that the combination of IDN, Shift, ...
    (microsoft.public.access.queries)
  • Re: Programming Question
    ... B-Days,Day Shift, Afternoon Shift, and B-Nights changed but not if the ... Although you ought to have a few more spaces in your SQL statement, basically this is a question of operator precedence, isn't it? ... it's better to use parentheses if necessary to ensure the order of evaluation you intend. ... Dirk Goldgar, MS Access MVP ...
    (microsoft.public.access.modulesdaovba)
  • Re: Picking only first record from records - QUITE BIG and COMPLEX
    ... If I understand your solution okay; ... This is not a table but a QUERY out of a table. ... > query may return more than one record for a given IDN and Shift. ...
    (microsoft.public.access.queries)
  • Re: Help with Query
    ... [FaultCode] ... [AssetID] ... dbo.LineFaultDescription.ID INNER JOIN ...
    (microsoft.public.sqlserver.programming)