Query filter based on min oper

[calculating additional data help]

Hello,

I have the following query with multiple Oper of the same WC, how do I query
so that it will only show the first Oper of the WC. Example below. Thanks

Oper WC
70 X50
80 X50
90 X50
10 X90
20 X90

Query return wanted:
Oper WC
70 X50
10 X90

 

[Duane Hookom]

There really isn't any "first" in SQL. It looks like Min will work for you:
SELECT Min([Oper]) As MinOper, WC
FROM tblNoNameGiven
GROUP BY WC;

 

[Marshall Barton]

I have the following query with multiple Oper of the same WC, how do I query
so that it will only show the first Oper of the WC. Example below. Thanks

Oper WC
70 X50
80 X50
90 X50
10 X90
20 X90

Query return wanted:
Oper WC
70 X50
10 X90

There is no such thing as a "first" record in a relational
database. Most times, you want the smallest or largest
value. Try using:

SELECT Min(Oper) As MinOper, WC
FROM sometablequery
GROUP BY WC

 

[calculating additional data help]

Marshall,

Thanks. I forgot to mentioned that there are also other fields such as
mach#, which will not work. Anyway, here is the modified actual data.

Oper WC Mach Order# Part#
70 X50 8200 301455 A123-01
80 X50 6400 301455 A123-01
90 X50 9500 301455 A123-01
10 X90 6000 305400 A209-05
20 X90 6500 305400 A209-05

Query return wanted:
Oper WC Mach Order# Part#
70 X50 8200 301455 A123-01
10 X90 6000 305400 A209-05

Sorry for not given enough info.

 

[calculating additional data help]

Duane,

Thanks. I forgot to mentioned that there are also other fields such as
mach#, which will not work. Anyway, here is the modified actual data.

Oper WC Mach Order# Part#
70 X50 8200 301455 A123-01
80 X50 6400 301455 A123-01
90 X50 9500 301455 A123-01
10 X90 6000 305400 A209-05
20 X90 6500 305400 A209-05

Query return wanted:
Oper WC Mach Order# Part#
70 X50 8200 301455 A123-01
10 X90 6000 305400 A209-05

Sorry for not given enough info.

There really isn't any "first" in SQL. It looks like Min will work for you:
SELECT Min([Oper]) As MinOper, WC
FROM tblNoNameGiven
GROUP BY WC;

--
Duane Hookom
Microsoft Access MVP

Hello,

I have the following query with multiple Oper of the same WC, how do I query
so that it will only show the first Oper of the WC. Example below. Thanks

Oper WC
70 X50
80 X50
90 X50
10 X90
20 X90

Query return wanted:
Oper WC
70 X50
10 X90

 

[Duane Hookom]

Try SQL like:

SELECT tblStillNotNamed.Oper, tblStillNotNamed.WC, tblStillNotNamed.Mach,
tblStillNotNamed.Order, tblStillNotNamed.Part
FROM tblStillNotNamed
WHERE tblStillNotNamed.Oper=(SELECT Min(Oper) FROM tblStillNotNamed s WHERE
tblStillNotNamed.[Order] = s.[order]);
--
Duane Hookom
Microsoft Access MVP

Duane,

Thanks. I forgot to mentioned that there are also other fields such as
mach#, which will not work. Anyway, here is the modified actual data.

Oper WC Mach Order# Part#
70 X50 8200 301455 A123-01
80 X50 6400 301455 A123-01
90 X50 9500 301455 A123-01
10 X90 6000 305400 A209-05
20 X90 6500 305400 A209-05

Query return wanted:
Oper WC Mach Order# Part#
70 X50 8200 301455 A123-01
10 X90 6000 305400 A209-05

Sorry for not given enough info.

There really isn't any "first" in SQL. It looks like Min will work for you:
SELECT Min([Oper]) As MinOper, WC
FROM tblNoNameGiven
GROUP BY WC;

--
Duane Hookom
Microsoft Access MVP

Hello,

I have the following query with multiple Oper of the same WC, how do I query
so that it will only show the first Oper of the WC. Example below. Thanks

Oper WC
70 X50
80 X50
90 X50
10 X90
20 X90

Query return wanted:
Oper WC
70 X50
10 X90

 

[Marshall Barton]

Thanks. I forgot to mentioned that there are also other fields such as
mach#, which will not work. Anyway, here is the modified actual data.

Oper WC Mach Order# Part#
70 X50 8200 301455 A123-01
80 X50 6400 301455 A123-01
90 X50 9500 301455 A123-01
10 X90 6000 305400 A209-05
20 X90 6500 305400 A209-05

Query return wanted:
Oper WC Mach Order# Part#
70 X50 8200 301455 A123-01
10 X90 6000 305400 A209-05

See Duane's response with the subquery in the Where clause.

Another way, possibly more efficient, would be:

SELECT T.*
FROM tblStillNotNamed As T
INNER JOIN (SELECT Min(X.Oper) As MinOper, X.WC
FROM tblStillNotNamed As X
GROUP BY X.WC) As M
ON T.WC = M.WC And T.Oper = M.MinOper

or, even simpler if Oper is the primary key field:

SELECT T.*
FROM tblStillNotNamed As T
INNER JOIN (SELECT Min(X.Oper) As MinOper
FROM tblStillNotNamed As X
GROUP BY X.WC) As M
ON T.Oper = M.MinOper