SQL Question

[Guest]

I have following table which is stored in MS Access database

Col1 Col2 Col3
A 3.2 25/10/2000 9:30:00 AM
A 2.1 25/10/2000 10:10:02 AM
A 1.5 25/10/2000 1:00:02 PM
A 2.3 25/10/2000 2:10:05 PM
A 10 26/10/2000 9:10:10 AM
A 1.2 26/10/2000 9:10:11 AM
A 4.8 26/10/2000 1:00:08 PM

I want to use SQL query so that I can achieve the following:

Problem 1: Pick only those records (At the most one per each day) based on
max value of Col2 (Max value should be within the context of one day). So it
should give me following result
set
A 3.2 25/10/2000 9:30:00 AM (Max value picked on Day 25/10/2000)
A 10 26/10/2000 9:10:10 AM (Max value picked on Day 26/10/2000)

Problem 2: Pick only those records (At the most one per each day) based on
min value of Col2 (Min value should be within the context of one day). So it
should give me following result
set
A 1.5 25/10/2000 1:00:02 PM (Min value picked on Day 25/10/2000)
A 1.2 26/10/2000 9:10:11 AM (Min value picked on Day 26/10/2000)

Problem 3: Pick only those records (At the most one per each day) based on
most recent value of Col3 (Most recent value value should be within the
context of one day). So it should give me following result
set
A 2.3 25/10/2000 2:10:05 PM (Most recent value(based on time) picked on
Day 25/10/2000)
A 4.8 26/10/2000 1:00:08 PM (Most recent value(based on time) picked on
Day 26/10/2000)



NOTE: In Oracle I can use set comparison. Is it possible to do that in MS
Access 2000 also. eg

SELECT * FROM employees
WHERE (first_name, last_name, email) IN
(('Guy', 'Himuro', 'GHIMURO'),('Karen', 'Colmenares', 'KCOLMENA'))

 

[Jamie Collins]

I have following table which is stored in MS Access database

Col1 Col2 Col3
A 3.2 25/10/2000 9:30:00 AM
A 2.1 25/10/2000 10:10:02 AM
A 1.5 25/10/2000 1:00:02 PM
A 2.3 25/10/2000 2:10:05 PM
A 10 26/10/2000 9:10:10 AM
A 1.2 26/10/2000 9:10:11 AM
A 4.8 26/10/2000 1:00:08 PM

I want to use SQL query so that I can achieve the following:

Problem 1: Pick only those records (At the most one per each day) based on
max value of Col2 (Max value should be within the context of one day). So it
should give me following result
set
A 3.2 25/10/2000 9:30:00 AM (Max value picked on Day 25/10/2000)
A 10 26/10/2000 9:10:10 AM (Max value picked on Day 26/10/2000)

You say 'At the most one per each day' but don't specify how to break
ties.

I've assume you are finding the max col2 per day of col3 for each col1:

SELECT Col1, MAX(Col2), INT(Col3) FROM employees GROUP BY Col1,
INT(Col3);

You don't want to group by col1, something more like:

SELECT T1.Col1, T1.Col2, T1.Col3 FROM employees AS T1 INNER JOIN (
SELECT Col1, MAX(Col2) AS max_col2, INT(Col3) FROM employees GROUP BY
Col1, INT(Col3)
) AS DT1 ON T1.Col2 = DT1.max_col2;

Problem 2: Pick only those records (At the most one per each day) based on
min value of Col2 (Min value should be within the context of one day). So it
should give me following result
set
A 1.5 25/10/2000 1:00:02 PM (Min value picked on Day 25/10/2000)
A 1.2 26/10/2000 9:10:11 AM (Min value picked on Day 26/10/2000)

As above but change MAX to MIN.

Problem 3: Pick only those records (At the most one per each day) based on
most recent value of Col3 (Most recent value value should be within the
context of one day). So it should give me following result
set
A 2.3 25/10/2000 2:10:05 PM (Most recent value(based on time) picked on
Day 25/10/2000)
A 4.8 26/10/2000 1:00:08 PM (Most recent value(based on time) picked on
Day 26/10/2000)

SELECT T1.Col1, T1.Col2, T1.Col3 FROM employees AS T1 INNER JOIN (
SELECT Col1, MAX(Col3) AS most_recent, INT(Col3)
FROM employees GROUP BY Col1, INT(Col3)
) AS DT1 ON DT1.Col1 = T1.Col1 and T1.Col3 = DT1.most_recent;

Again, you may want to omit Col1 from the GROUP BY.

Jamie.

--

 

[Guest]

Thanks for the reply. Here is the solution which is proposed by another guy.
This worked just fine.

problem 1
Code:
select Col1
, Col2
, Col3
from yourtable as X
where Col2
= ( select max(Col2)
from yourtable
where format(Col3,'yyyymmdd')
= format(X.Col3.'yyyymmdd') )

problem 2 change max to min above

problem 3
Code:
select Col1
, Col2
, Col3
from yourtable as X
where Col3
= ( select max(Col3)
from yourtable
where format(Col3,'yyyymmdd')
= format(X.Col3,'yyyymmdd') )



KDV