Ignore other records but first (highest) record

[annysjunkmail]

Hi Group,

Is it possible to run a query which returns only the first record where
the first record is the highest record.

For example, I have data like so...

ID Percentage
1 80
1 20
2 50
2 50
3 75
3 25

I just need to see...

ID Percentage
1 80
2 50
3 75


Thanks
Tony

 

[Marshall Barton]

Is it possible to run a query which returns only the first record where
the first record is the highest record.

For example, I have data like so...

ID Percentage
1 80
1 20
2 50
2 50
3 75
3 25

I just need to see...

ID Percentage
1 80
2 50
3 75

For this example (where there are no other fields involved),
the easy way is to use:

SELECT ID, Max(Precent) As Highest
FROM thetable
GROUP BY ID

 

[annysjunkmail]

Thanks for your reply Marshall.
Finished work about 2 hours ago so I am replying from home (I just love
my job!!!).
I can't test the solution until tomorrow. In advance, there are other
fields(9) and tables(7) that make up my query - will this prove to be a
problem or will the logic of using the max function apply?

Thanks
Tony

 

[Marshall Barton]

Thanks for your reply Marshall.
Finished work about 2 hours ago so I am replying from home (I just love
my job!!!).
I can't test the solution until tomorrow. In advance, there are other
fields(9) and tables(7) that make up my query - will this prove to be a
problem or will the logic of using the max function apply?

Maybe, maybe not ;-)

If the fields are simple values that are the same across
every record with the same ID and no memo fields, then you
can group by all the fields.

That situation is rather unlikely. The query to do the more
complex situations depend on the details of your tables and
fields. I don't think it's worth anyone's time for me to
speculate so I'll wait for you to provide the query's SQL
statement and an explanation of each field (name, type, and
how its value relates to the ID field).

 

[Guest]

Sorry to butt in the conversation but I was hoping you could speculate and on
the more comlex: I have 4 columns as listed below:

Bill of Lading
Shipment Status
Status Date
Carrier

The following three rows of data are present:

BOL Shipment Status Status Date Carrier
1234 L 1/27/2006 0900am A
1234 L 1/27/2006 1500pm B

I want to be able to pull the earliest date for bol1234 where status is L
and show the carrier associated. If I use the min function and try to show
the carrier, I get both rows pulled.

Might you be able to help?

Thankfully,

Tim

 

[Marshall Barton]

Maybe not a good example?? It's not much more complicated
to allow for more than one BOL.

SELECT T.*
FROM table As T
WHERE T.[Shipment Status] = "L"
AND T.[Status Date] =
(SELECT Min(X.[Statue Date]
FROM table As X
WHERE X.BOL = T.BOL
AND X.[Shipment Status] = T.[Shipment Status]
)

 

[annysjunkmail]

Thanks Marshall,
worked a treat

Tony

 

[Guest]

Thanks for the help. I understand how the SQL will work below just from
looking at it, and it looks like it will work for my situation. However,
there are instances where I will have 2 lines of data to compare and
instances where I may have 3 lines of data to compare. Is there a way to
handle all of that in one?

Maybe not a good example?? It's not much more complicated
to allow for more than one BOL.

SELECT T.*
FROM table As T
WHERE T.[Shipment Status] = "L"
AND T.[Status Date] =
(SELECT Min(X.[Statue Date]
FROM table As X
WHERE X.BOL = T.BOL
AND X.[Shipment Status] = T.[Shipment Status]
)
--
Marsh
MVP [MS Access]

Sorry to butt in the conversation but I was hoping you could speculate and on
the more comlex: I have 4 columns as listed below:

Bill of Lading
Shipment Status
Status Date
Carrier

The following three rows of data are present:

BOL Shipment Status Status Date Carrier
1234 L 1/27/2006 0900am A
1234 L 1/27/2006 1500pm B

I want to be able to pull the earliest date for bol1234 where status is L
and show the carrier associated. If I use the min function and try to show
the carrier, I get both rows pulled.

 

[Marshall Barton]

I don't understand what you mean by "lines of data to
compare". The suggested query doesn't care how many records
have the same BOL. And it will work for as many different
BOL values are in the table.

Could you update your example with some data that
demonstrates what the query is not doing what you want?
--
Marsh
MVP [MS Access]

Thanks for the help. I understand how the SQL will work below just from
looking at it, and it looks like it will work for my situation. However,
there are instances where I will have 2 lines of data to compare and
instances where I may have 3 lines of data to compare. Is there a way to
handle all of that in one?

Maybe not a good example?? It's not much more complicated
to allow for more than one BOL.

SELECT T.*
FROM table As T
WHERE T.[Shipment Status] = "L"
AND T.[Status Date] =
(SELECT Min(X.[Statue Date]
FROM table As X
WHERE X.BOL = T.BOL
AND X.[Shipment Status] = T.[Shipment Status]
)

Sorry to butt in the conversation but I was hoping you could speculate and on
the more comlex: I have 4 columns as listed below:

Bill of Lading
Shipment Status
Status Date
Carrier

The following three rows of data are present:

BOL Shipment Status Status Date Carrier
1234 L 1/27/2006 0900am A
1234 L 1/27/2006 1500pm B

I want to be able to pull the earliest date for bol1234 where status is L
and show the carrier associated. If I use the min function and try to show
the carrier, I get both rows pulled.


(e-mail address removed) wrote:
Thanks for your reply Marshall.
Finished work about 2 hours ago so I am replying from home (I just love
my job!!!).
I can't test the solution until tomorrow. In advance, there are other
fields(9) and tables(7) that make up my query - will this prove to be a
problem or will the logic of using the max function apply?


:
Maybe, maybe not ;-)

If the fields are simple values that are the same across
every record with the same ID and no memo fields, then you
can group by all the fields.

That situation is rather unlikely. The query to do the more
complex situations depend on the details of your tables and
fields. I don't think it's worth anyone's time for me to
speculate so I'll wait for you to provide the query's SQL
statement and an explanation of each field (name, type, and
how its value relates to the ID field).