Query filter based on min oper

  • Thread starter Thread starter calculating additional data help
  • Start date Start date
C

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
 
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;
 
calculating said:
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
Click to expand...


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
 
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.
 
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.


Duane Hookom said:
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


calculating additional data help said:
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
Click to expand...
Click to expand...
 
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


calculating additional data help said:
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.


Duane Hookom said:
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


calculating additional data help said:
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
Click to expand...
Click to expand...
Click to expand...
 
calculating said:
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
Click to expand...


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
 
Back
Top