Make select with only the first occurence?

I have two tables.

SHIPMENTS (SH)
shipm_ID
ref_ID

ITEMS (IT)
it_ID
ship_ID
it_name
it_chassisno

One shipment can contain more items.

When I now do this select I get all items per shipment.

SELECT SH.shipm_ID, IT.it_name
FROM SH
INNER JOIN IT
ON SH.shipm_ID = IT.shipm_ID;

How can I make a select where I get only the first occurence of an item.

So a JOIN and TOP 1 combined.

mvgilsAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
cyberkiwiConnect With a Mentor Commented:
I fixed up the query and included it in the attached.

This query (qryCombined) does it in one go, but cannot be viewed in the Query Design view
SELECT SH.shipm_ID, IT.it_name
FROM ((
        SELECT SH.shipm_ID, MIN(IT.it_ID) AS minID
        FROM SHIPMENTS AS SH
        INNER JOIN ITEMS AS IT ON SH.shipm_ID=IT.ship_ID
        GROUP BY SH.shipm_ID)  AS X
INNER JOIN SHIPMENTS AS SH ON SH.shipm_ID=X.shipm_ID)
INNER JOIN ITEMS AS IT ON (IT.it_ID=X.minID) AND (IT.ship_ID=X.shipm_ID);

Open in new window

This query (qryMinShipItemID) creates the inner part that finds the min item id per shipment
SELECT SH.shipm_ID, MIN(IT.it_ID) AS minID
FROM SHIPMENTS AS SH
INNER JOIN ITEMS AS IT ON SH.shipm_ID = IT.ship_ID
GROUP BY SH.shipm_ID;

Open in new window

And this (qry2Steps) uses the query above to produce the final result.
Using the last 2 queries may be easier to understand since they can be viewed in Query design view
SELECT SH.shipm_ID, IT.it_name
FROM (qryMinShipItemID AS X
INNER JOIN SHIPMENTS AS SH ON SH.shipm_ID=X.shipm_ID)
INNER JOIN ITEMS AS IT ON (IT.ship_ID=X.shipm_ID) AND (IT.it_ID=X.minID);

Open in new window

Database7.zip
0
 
pdd1lanCommented:
SELECT FIRST(SH.shipm_ID), IT.it_name
FROM SH
INNER JOIN IT
ON SH.shipm_ID = IT.shipm_ID
GROUP BY T.it_name

OR

SELECT TOP (1) SH.shipm_ID, IT.it_name
FROM SH
INNER JOIN IT
ON SH.shipm_ID = IT.shipm_ID
ORDER BY SH.shipm_ID
0
 
Bhavesh ShahLead AnalysistCommented:
Hi,

if its sql server then easily we can do...

but in access, We can do this way
SELECT SH.shipm_ID, Min(IT.it_name)as it_name
FROM SH
INNER JOIN IT
ON SH.shipm_ID = IT.shipm_ID
GROUP BY SH.shipm_ID;

Open in new window

0
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Brichsoft's approach works if you need "random" data for each ID (here the "smallest" item name). If you need more data, and exact that of the first occurance sorted by date, it is getting more complex.
In addition you need to tell us which SQL DBMS you are using - Access, SQL Server, ...?
There is a more general ANSI SQL syntax, but not all DBMS will allow for using it.
0
 
cyberkiwiCommented:
SELECT SH.shipm_ID, IT.it_name
FROM SH
INNER JOIN IT
ON SH.shipm_ID = IT.shipm_ID;

>> How can I make a select where I get only the first occurence of an item.
So, you want to group by the shipment, e.g.

SELECT SH.shipm_ID, FIRST(IT.it_name) AS it_name
FROM SH
INNER JOIN IT
ON SH.shipm_ID = IT.shipm_ID
GROUP BY SH.shipm_ID

Access allows FIRST() which takes just one result that it comes across IN NO PARTICULAR ORDER, i.e. pseudo-RANDOM
If you need the TOP(1) in a particular order, then you need a more complicated query and whether it is possible or not in Access will depend on what other fields are in the IT table.
0
 
mvgilsAuthor Commented:
I'm using ACCESS 2010. I put it in a tag but not really visible in my question.

I will try to explain what I want to store.

I have a shipping company that ships vehicles/trucks/machines.
A shipment is a transport form port A to port B for a certain price.
When a shipment contains 1 item, it means that we ship 1 vehicle.
When a shipment contains two or more items it is usually 1 truck (the base unit) that contains one or more loaded passenger cars.
The transport fee (our invoice) is the base unit and the passenger cars are also transported for a small extra since they do not occupy more space on the ship.

My first item in the IT table with the corresponding shipping id is always the base unit, is my line of thinking.
But i'm not 100% sure if this is correct database thinking??
Or should I create an extra field in ITEMS to define it as a base unit??

The full IT table is:

ITEMS (IT)
it_ID      (items ID)
ship_ID    (shipment ID)
it_name    (brand)
it_type      (type, model)
it_chassisno (VIN)
it_registration_nr. (registration)
it_weight   (weigth)
it_value     (value)

samples values:
1, ship_001, BMW, 525, WDB456456TY676767, WE-RT-45, 1250 kg., USD 2.500,=
2, ship_002, PETERBILT, 410, WDB456456TY676768, WE-RT-46, 12500 kg., USD 7.500,=
3, ship_002, FORD, EXPLORER, WDB456456TY676769, WE-RT-47, 1500 kg., USD 3.500,=

hopefully now you understand my data requirements!
0
 
cyberkiwiCommented:
Then FIRST() won't cut it since it is quite random.
Use this instead

SELECT SH.shipm_ID, IT.it_name
FROM
(
SELECT SH.shipm_ID, MIN(IT.it_ID) AS minID
FROM SH
INNER JOIN IT
ON SH.shipm_ID = IT.shipm_ID
GROUP BY SH.shipm_ID) as X
INNER JOIN SH on SH.shipm_ID = X.shipm_ID
INNER JOIN IT on IT.shipm_ID = X.shipm_ID and IT.it_ID=X.it_ID
0
 
mvgilsAuthor Commented:
Hello cyberkiwi
I cannot get your suggestion to work in ACCESS2010.

The give me an error message:
Syntax error (operator is missing) in query expression SH.shipm_ID = X.shipm_ID
INNER JOIN IT on IT.shipm_ID = X.shipm_I.

Coluld you maybe check your syntax or try to explain me how you constructed this sql statement?

thanks!
0
 
mvgilsAuthor Commented:
Thank you for the big effort and elaborate explanation, this realy helps my learning process ;-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.