Selecting the first of each group problem

  • Thread starter Thread starter James Minns
  • Start date Start date
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
 
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
 
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
 
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
 
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...
 
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
 
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...
 
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...
 
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...
 
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...
 
Back
Top