show the attributes of the selected records

[Jenny]

I have a table with flow rate and datetime attributes.
I used a "select Min(flowrate) from table group by Datepart(day of
DateTime)" query to get the min flow rate every day. But I also want to know
the time for that minimum flow rate. But I don't want to use "Time" in
grouping by or aggregation function. The SELECT doesn't allow me to show the
Time. How can I get the time for the min flow rate?

 

[Marshall Barton]

I have a table with flow rate and datetime attributes.
I used a "select Min(flowrate) from table group by Datepart(day of
DateTime)" query to get the min flow rate every day. But I also want to know
the time for that minimum flow rate. But I don't want to use "Time" in
grouping by or aggregation function. The SELECT doesn't allow me to show the
Time. How can I get the time for the min flow rate?

If you only want to select the one record per day, you could
use something like:

SELECT *
FROM table
WHERE (SELECT Min(X.flowrate)
FROM table As X
WHERE DateValue(X.datetime) = DateValue(table.datetime))

 

[Jenny]

Marshall,
It seems my reply was not posted. I tried your method. But it doesn't work
for me. The results are all the records, but I want only the records for the
minimum flow rate each day. And it is very time consuming. I used 1000
records in the table to test the code. It took around one minute to finish.
but I actually have 30,000 records.

Could you help check if there is sth wrong in my codes? Thank you! Again,
What I want to do is to get the DateTime for the minimum flowrate record. I
did the query by grouping the data by day and take the minumum.

My codes are:
SELECT Ttable.DateTime, Ttable.QFINAL
FROM Ttable
WHERE (SELECT Min(XTABLE.QFINAL)
FROM Ttable AS XTABLE
WHERE DateValue(Ttable.DateTime) = DateValue(XTABLE.DateTime)
GROUP BY DatePart("yyyy",[XTABLE].[DateTime]) & "/" &
Format(DatePart("m",[XTABLE].[DateTime]),"00") & "/" &
Format(DatePart("d",[XTABLE].[DateTime]),"00"));

 

[Jenny]

PS:

I tried Ttable.DateTime=XTABLE.DateTime in the WHERE clause instead of
DateValue(Ttable.DateTime)=DateValue(XTABLE.DateTime) in my last reply,
since I want the DateTime for the minimum flow record. But it still doesn't
work.

 

[Marshall Barton]

It seems my reply was not posted. I tried your method. But it doesn't work
for me. The results are all the records, but I want only the records for the
minimum flow rate each day. And it is very time consuming. I used 1000
records in the table to test the code. It took around one minute to finish.
but I actually have 30,000 records.

Could you help check if there is sth wrong in my codes? Thank you! Again,
What I want to do is to get the DateTime for the minimum flowrate record. I
did the query by grouping the data by day and take the minumum.

My codes are:
SELECT Ttable.DateTime, Ttable.QFINAL
FROM Ttable
WHERE (SELECT Min(XTABLE.QFINAL)
FROM Ttable AS XTABLE
WHERE DateValue(Ttable.DateTime) = DateValue(XTABLE.DateTime)
GROUP BY DatePart("yyyy",[XTABLE].[DateTime]) & "/" &
Format(DatePart("m",[XTABLE].[DateTime]),"00") & "/" &
Format(DatePart("d",[XTABLE].[DateTime]),"00"));

You kind of made a mess of the query I posted ;-)

Try this without any changes (i.e. use Copy/Paste):

SELECT Ttable.DateTime, Ttable.QFINAL
FROM Ttable
WHERE Ttable.QFINAL = (SELECT Min(XTABLE.QFINAL)
FROM Ttable AS XTABLE
WHERE DateValue(Ttable.DateTime) =
DateValue(XTABLE.DateTime)

 

[Jenny]

Thank you a lot, Marshall. It works for my test data!

Jenny

It seems my reply was not posted. I tried your method. But it doesn't work
for me. The results are all the records, but I want only the records for the
minimum flow rate each day. And it is very time consuming. I used 1000
records in the table to test the code. It took around one minute to finish.
but I actually have 30,000 records.

Could you help check if there is sth wrong in my codes? Thank you! Again,
What I want to do is to get the DateTime for the minimum flowrate record. I
did the query by grouping the data by day and take the minumum.

My codes are:
SELECT Ttable.DateTime, Ttable.QFINAL
FROM Ttable
WHERE (SELECT Min(XTABLE.QFINAL)
FROM Ttable AS XTABLE
WHERE DateValue(Ttable.DateTime) = DateValue(XTABLE.DateTime)
GROUP BY DatePart("yyyy",[XTABLE].[DateTime]) & "/" &
Format(DatePart("m",[XTABLE].[DateTime]),"00") & "/" &
Format(DatePart("d",[XTABLE].[DateTime]),"00"));

You kind of made a mess of the query I posted ;-)

Try this without any changes (i.e. use Copy/Paste):

SELECT Ttable.DateTime, Ttable.QFINAL
FROM Ttable
WHERE Ttable.QFINAL = (SELECT Min(XTABLE.QFINAL)
FROM Ttable AS XTABLE
WHERE DateValue(Ttable.DateTime) =
DateValue(XTABLE.DateTime)

 

[Jenny]

Thank you Marshall again. Could you let me know how it gets the DateTime for
the minimum daily QFINAL record?
This is your codes:
SELECT Ttable.DateTime, Ttable.QFINAL
FROM Ttable
WHERE Ttable.QFINAL = (SELECT Min(XTABLE.QFINAL)
FROM Ttable AS XTABLE
WHERE DateValue(Ttable.DateTime) =
DateValue(XTABLE.DateTime))

Will the sub SELECT return an ID for Min(XTABLE.QFINAL) record to search in
the Ttable? Or only return the Value of Min(XTable.QFINAL) to search for the
same value for QFINAL in Ttable? It is possible QFINALs may have same values
for different records. Then it might have error in result if ony using the
value of QFINAL to search for record.

Thank you!

Jenny

 

[Marshall Barton]

Thank you Marshall again. Could you let me know how it gets the DateTime for
the minimum daily QFINAL record?
This is your codes:
SELECT Ttable.DateTime, Ttable.QFINAL
FROM Ttable
WHERE Ttable.QFINAL = (SELECT Min(XTABLE.QFINAL)
FROM Ttable AS XTABLE
WHERE DateValue(Ttable.DateTime) =
DateValue(XTABLE.DateTime))

Will the sub SELECT return an ID for Min(XTABLE.QFINAL) record to search in
the Ttable? Or only return the Value of Min(XTable.QFINAL) to search for the
same value for QFINAL in Ttable? It is possible QFINALs may have same values
for different records. Then it might have error in result if ony using the
value of QFINAL to search for record.

First, I apologize for saying you made a mess of the query
when I meant to say **I** made a mess of it.

The subquery only return the value of Min(XTable.QFINAL) and
if there are multiple records for the same day and the same
minimum QFINAL, then you will get more than one record in
the query's dataset. That might not be what you want, but
by itself, will not cause an error in the query. If it is
not what you want, then I would need to know which one of
the same QFINAL records you want.

 

[Jenny]

I only worried if there are mutiple records with the same minimum QFINAL on
different days. If there are multiple records for the same day and the same
minimum QFINAL, it is fine. I ran the codes using the test data having two
days with same minimum QFINAL records. The codes work successfully! I am new
to database, and I want to figure out how it works. I thought the codes will
get the values of Minimum QFINAL from sub SELECT result and match it to a
record with the same QFINAL value in Ttable. But it seems the sub SELECT
result also contains the Date information to get the record of same value for
QFINAL for that date in Ttable. Is that correct?

 

[Marshall Barton]

I only worried if there are mutiple records with the same minimum QFINAL on
different days. If there are multiple records for the same day and the same
minimum QFINAL, it is fine. I ran the codes using the test data having two
days with same minimum QFINAL records. The codes work successfully! I am new
to database, and I want to figure out how it works. I thought the codes will
get the values of Minimum QFINAL from sub SELECT result and match it to a
record with the same QFINAL value in Ttable. But it seems the sub SELECT
result also contains the Date information to get the record of same value for
QFINAL for that date in Ttable. Is that correct?

Yes, that is correct. The purpose of the Where clause in
the subquery is to "clump" all the records for each day.

 

[Jenny]

Thank you for your reply. I have one more problem. The query is very very
time consuming. I have 9 years data. One redord every 15 minute. When I only
try do query for one year(about 24*4*365=30500 records). It takes even more
than an hour to run the query. Why is it so slow?


Jenny

 

[Marshall Barton]

Thank you for your reply. I have one more problem. The query is very very
time consuming. I have 9 years data. One redord every 15 minute. When I only
try do query for one year(about 24*4*365=30500 records). It takes even more
than an hour to run the query. Why is it so slow?

Because it has tremendous amount of work to do ;-)

The core reason for slowness in these queries is because the
DateValue function prevents indexing from optimizing the
Where clause. Hence, the queries must process each and
every record (called a table scan) to determine if the Where
(or On) condition is true or not. Using the subquery in the
Where clause causes the subquery to be redone for each and
every record in the main query so it's actually processing
over 90,000,000 records.

Let's try an alternate query that should dramatically reduce
the number of records that have to be processed:

SELECT Ttable.DateTime, Ttable.QFINAL
FROM Ttable
INNER JOIN (SELECT DateValue(X.DateTime) As DayMin,
Min(X.QFINAL)
FROM Ttable As X
GROUP BY DateValue(X.DateTime)
) As XTABLE
ON DateValue(Ttable.DateTime) = XTABLE.DayMin

 

[John W. Vinson]

Thank you for your reply. I have one more problem. The query is very very
time consuming. I have 9 years data. One redord every 15 minute. When I only
try do query for one year(about 24*4*365=30500 records). It takes even more
than an hour to run the query. Why is it so slow?

Maybe you need indexes on the fields used for criteria, for sorting, or for
joining; maybe you can cut down on the number of function calls. Please post
the current SQL view of the query, maybe someone can suggest a way to speed it
up.

 

[Jenny]

Thank you for the alternative codes. But it acutally returned all the
records, not the records for daily minimum QFINAL.

 

[Jenny]

I modified your codes to add another critirion for select on.
ON (Ttable.QFINAL = XTABLE.MinQFINAL AND DateValue(Ttable.DateTime) =
XTABLE.DayMin)
One more question is that I want to include in the query that:
if there is a record with QFINAL= zero beteew 2:00AM to 7AM for a day then I
do not want the minimum QFINAL record for that day. How to do that?

it returned the minimum daily QFINAL but with duplicates records. Here are
the codes:

SELECT DISTINCTIVE Ttable.DateTime, Ttable.QFINAL
FROM Ttable INNER JOIN
(SELECT DateValue(X.DateTime) AS DayMin, Min(X.QFINAL) AS MinQFINAL
FROM Ttable AS X
GROUP BY DateValue(X.DateTime)) AS XTABLE ON (Ttable.QFINAL =
XTABLE.MinQFINAL AND DateValue(Ttable.DateTime) = XTABLE.DayMin)

 

[Marshall Barton]

I modified your codes to add another critirion for select on.
ON (Ttable.QFINAL = XTABLE.MinQFINAL AND DateValue(Ttable.DateTime) =
XTABLE.DayMin)
One more question is that I want to include in the query that:
if there is a record with QFINAL= zero beteew 2:00AM to 7AM for a day then I
do not want the minimum QFINAL record for that day. How to do that?

it returned the minimum daily QFINAL but with duplicates records. Here are
the codes:

SELECT DISTINCTIVE Ttable.DateTime, Ttable.QFINAL
FROM Ttable INNER JOIN
(SELECT DateValue(X.DateTime) AS DayMin, Min(X.QFINAL) AS MinQFINAL
FROM Ttable AS X
GROUP BY DateValue(X.DateTime)) AS XTABLE ON (Ttable.QFINAL =
XTABLE.MinQFINAL AND DateValue(Ttable.DateTime) = XTABLE.DayMin)

Good catch with the missing ON condition. You're getting
pretty good at fixing up my garbled queries ;-)

You can apply that kind of restriction by adding a Where
clause to the subquery:
. . .
FROM Ttable AS X
WHERE Not (X.QFINAL= 0
And TimeValue(X.DateTime) Between #2:00# And #7:00#)
GROUP BY DateValue(X.DateTime))
. . .

Did putting the subquery in the Join run significantly
faster than when it was in the Where clause? If it did, is
its speed acceptable?

 

[Jenny]

Yes, it is significantly faster. Thank you!
I have one more question.
I think, your suggestion to addin a where clause won't solve my problem.
FROM Ttable AS X
WHERE Not (X.QFINAL= 0
And TimeValue(X.DateTime) Between #2:00# And #7:00#)
GROUP BY DateValue(X.DateTime))

I believe this only excludes the records with QFINAL=0 between 2:00-7:00. My
criterion is: if there is at least one record have zero within that range at
a date, than I dont want all the data for that date.

Is it clear for you? I can't find a way to do that by count.Could you help me?
Thank you!

Jenny

 

[Jenny]

I tried your codes. It didn't work as I gusessed. Now, I am considering to do
it this way.
What I want is to exclude the date if there is any bad quality data in that
date.
I am considering if I can generate a table BADDATES containing the dates
which have at least one Bad record for QFINAL ( I actually used an attribute
QFINALFlag="B"). Then do the query by excluding a record if its date is among
the BADDATES table?
That's just a idea, but I don't know how to realize it using query.
Is it possible to do it this way or you have better idea?
Thank you!

Jenny

 

[Jenny]

One more question. How to remove the mutiple record when a date has two
records for the minimum QFINAL. I want to keep the one with smallest value
for Time. How to do that?

 

[Marshall Barton]

One more question. How to remove the mutiple record when a date has two
records for the minimum QFINAL. I want to keep the one with smallest value
for Time. How to do that?

I don't think you need another table. Assuming the 2am to
5am restiction is no longer a consideration, try this kind
of thing:

SELECT DISTINCT Ttable.DateTime, Ttable.QFINAL
FROM Ttable INNER JOIN
(SELECT DateValue(X.DateTime) AS DayMin,
Min(X.QFINAL) AS MinQFINAL
FROM Ttable AS X
GROUP BY DateValue(X.DateTime)
HAVING Min(X.QFINAL) > 0
) AS XTABLE
ON Ttable.QFINAL = XTABLE.MinQFINAL
And DateValue(Ttable.DateTime) = XTABLE.DayMin

Note that it's DISTINCT, not DISTINCTIVE. At least I have
never seen any documentation that mentions a DISTINCTIVE
predicate.