Selecting the first of each group problem

J

James Minns

Hi All!

I have the folowing problem with a query:

Here is the table (part of)

Group Time/Date Description
A 01/01/2001 James
A 02/01/2001 Helen
A 03/01/2001 Elena
A 04/01/2001 Richard
B 02/01/2001 Arthur
B 03/01/2001 Barry
C 02/01/2001 Harold

I need a query that will select the first record of each group sorted on
Time/Data field, like this:
Group Time/Date Description
A 01/01/2001 James
B 02/01/2001 Arthur
C 02/01/2001 Harold

Is it possible? Thanks for any help,

James
 
N

Nikos Yannacopoulos

James,

It's not just possible, it's quite easy too! Start by making a new query in
design view, add the table and drag all three fields down to the grid. The
go View > Totals, and notice the new line headed Total: that appears in the
grid, the default setting being Group By in all fields. Leave the Group
setting as is, and change the date field 's setting to Min, and the
Description field's setting to First and you are done!

HTH,
Nikos
 
J

James Minns

Nikos Yannacopoulos said:
James,

It's not just possible, it's quite easy too! Start by making a new query
in
design view, add the table and drag all three fields down to the grid. The
go View > Totals, and notice the new line headed Total: that appears in
the
grid, the default setting being Group By in all fields. Leave the Group
setting as is, and change the date field 's setting to Min, and the
Description field's setting to First and you are done!

HTH,
Nikos
Click to expand...

I'm kicking myself... so easy :)
Thanks!

James
 
J

John Spencer (MVP)

Pardon me for jumping in.

The solution given was simple, but it can and probably will return incorrect
results. The First function does not necessarily return data from the same
record as the one that holds the Min datetime. You probably need to use a
coordinated subquery, such as the untested sample one below.

SELECT GROUP, [Time/Date], Description
FROM YourTable
WHERE [Time/Date] =
(SELECT Min([Time/Date]
FROM YourTable as Tmp
WHERE Tmp.Group = YourTable.Group)

You can do the same with TWO queries. Using the grid, you would do the following.

Open a new query
Add the table and drag the Group and Date field into the grid
Use View: Totals
Leave Group as Group By in the totals line
Change the Date field to Min
Save this as qryMinDateForGroup

Open another new query
Add the table and qryMinDateForGroup
Join the table to the query on both Group and Date fields
(Drag from the Group in the table to the group in the query, then drag from the
date in the table to the minimum date in the query)
Add the fields you want to display.
Save the query and run it whenever you want. It will automatically
 
N

Nikos Yannacopoulos

John,

Thanks for this, absolutely silly of me!

Nikos

John Spencer (MVP) said:
Pardon me for jumping in.

The solution given was simple, but it can and probably will return incorrect
results. The First function does not necessarily return data from the same
record as the one that holds the Min datetime. You probably need to use a
coordinated subquery, such as the untested sample one below.

SELECT GROUP, [Time/Date], Description
FROM YourTable
WHERE [Time/Date] =
(SELECT Min([Time/Date]
FROM YourTable as Tmp
WHERE Tmp.Group = YourTable.Group)

You can do the same with TWO queries. Using the grid, you would do the following.

Open a new query
Add the table and drag the Group and Date field into the grid
Use View: Totals
Leave Group as Group By in the totals line
Change the Date field to Min
Save this as qryMinDateForGroup

Open another new query
Add the table and qryMinDateForGroup
Join the table to the query on both Group and Date fields
(Drag from the Group in the table to the group in the query, then drag from the
date in the table to the minimum date in the query)
Add the fields you want to display.
Save the query and run it whenever you want. It will automatically

James said:
I'm kicking myself... so easy :)
Thanks!

James
Click to expand...
Click to expand...
 
J

James Minns

John Spencer (MVP) said:
SELECT GROUP, [Time/Date], Description
FROM YourTable
WHERE [Time/Date] =
(SELECT Min([Time/Date]
FROM YourTable as Tmp
WHERE Tmp.Group = YourTable.Group)
Click to expand...

Another gem for my SQL knowledge base! Thanks a lot.
James
 
G

Guest

My requirement is different and it using below sql is not working.

Table
record id date ID AMT
1 5/11/2005 1 500
4 5/11/2005 1 600
5 5/11/2005 1 400
6 5/11/2005 4 1000
8 5/11/2005 4 800
9 5/11/2005 7 800
20 5/11/2005 7 800
21 5/11/2005 7 700
24 5/11/2005 8 900

Output
record id date ID AMT
1 5/11/2005 1 500
6 5/11/2005 4 1000
9 5/11/2005 7 800
24 5/11/2005 8 900

Is there way to get above output ?





James Minns said:
John Spencer (MVP) said:
SELECT GROUP, [Time/Date], Description
FROM YourTable
WHERE [Time/Date] =
(SELECT Min([Time/Date]
FROM YourTable as Tmp
WHERE Tmp.Group = YourTable.Group)
Click to expand...

Another gem for my SQL knowledge base! Thanks a lot.
James
Click to expand...
 
G

Guest

Can I get below output

Table
Recordid date id amt
1 5/10/2005 1 1000
4 5/10/2005 1 600
5 5/10/2005 1 700
7 5/10/2005 2 900
8 5/10/2005 2 400
9 5/10/2005 2 300
10 5/10/2005 4 700
11 5/10/2005 4 600
12 5/10/2005 4 1000
13 5/10/2005 5 600


Ouput Table
Recordid date id amt
1 5/10/2005 1 1000
7 5/10/2005 2 900
10 5/10/2005 4 700
13 5/10/2005 5 600

Need help

James Minns said:
John Spencer (MVP) said:
SELECT GROUP, [Time/Date], Description
FROM YourTable
WHERE [Time/Date] =
(SELECT Min([Time/Date]
FROM YourTable as Tmp
WHERE Tmp.Group = YourTable.Group)
Click to expand...

Another gem for my SQL knowledge base! Thanks a lot.
James
Click to expand...
 
G

Guest

hi,
My requirement is different looking as per below output.

Table
Recordid date id amt
1 5/10/2005 1 1000
4 5/10/2005 1 600
5 5/10/2005 1 700
7 5/10/2005 2 900
8 5/10/2005 2 400
9 5/10/2005 2 300
10 5/10/2005 4 700
11 5/10/2005 4 600
12 5/10/2005 4 1000
13 5/10/2005 5 600

OUTPUT
Recordid date id amt
1 5/10/2005 1 1000
7 5/10/2005 2 900
10 5/10/2005 4 700
13 5/10/2005 5 600

Can I get above output ?



James Minns said:
John Spencer (MVP) said:
SELECT GROUP, [Time/Date], Description
FROM YourTable
WHERE [Time/Date] =
(SELECT Min([Time/Date]
FROM YourTable as Tmp
WHERE Tmp.Group = YourTable.Group)
Click to expand...

Another gem for my SQL knowledge base! Thanks a lot.
James
Click to expand...
 
D

Duane Hookom

SELECT *
FROM Table
WHERE [Record ID] =
(SELECT Min([Record ID])
FROM Table T
WHERE T.ID = Table.ID)


--
Duane Hookom
MS Access MVP
--

cap said:
My requirement is different and it using below sql is not working.

Table
record id date ID AMT
1 5/11/2005 1 500
4 5/11/2005 1 600
5 5/11/2005 1 400
6 5/11/2005 4 1000
8 5/11/2005 4 800
9 5/11/2005 7 800
20 5/11/2005 7 800
21 5/11/2005 7 700
24 5/11/2005 8 900

Output
record id date ID AMT
1 5/11/2005 1 500
6 5/11/2005 4 1000
9 5/11/2005 7 800
24 5/11/2005 8 900

Is there way to get above output ?





James Minns said:
John Spencer (MVP) said:
SELECT GROUP, [Time/Date], Description
FROM YourTable
WHERE [Time/Date] =
(SELECT Min([Time/Date]
FROM YourTable as Tmp
WHERE Tmp.Group = YourTable.Group)
Click to expand...

Another gem for my SQL knowledge base! Thanks a lot.
James
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Query, Ranges time group by concept 1
SELECT Statement Question 5
sql statement 1
Selecting whole records while using a MaxOf function. 5
Counts number of entries per day in query 4
Relateive maximum/ price value of a date 1
Duplicate a set of rows for each item (subdatasheet?) 0
Looking up across date ranges in Access 2

Top