[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Make select with only the first occurence?

Posted on 2010-11-28
9
Medium Priority
?
667 Views
Last Modified: 2012-05-10
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.

0
Comment
Question by:mvgils
9 Comments
 
LVL 8

Expert Comment

by:pdd1lan
ID: 34225245
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
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 34225257
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
 
LVL 72

Expert Comment

by:Qlemo
ID: 34225297
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34226010
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
 

Author Comment

by:mvgils
ID: 34226617
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
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34226698
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
 

Author Comment

by:mvgils
ID: 34277006
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
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 500 total points
ID: 34278185
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
 

Author Closing Comment

by:mvgils
ID: 34278846
Thank you for the big effort and elaborate explanation, this realy helps my learning process ;-)
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
Among the most obnoxious of Exchange errors is error 1216 – Attached Database Mismatch error of the Jet Database Engine. When faced with this error, users may have to suffer from mailbox inaccessibility and in worst situations, permanent data loss.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Hi, this video explains a free download that you can incorporate into your Access databases, or use stand-alone for contact management. Contacts -- Names, Addresses, Phone Numbers, eMail Addresses, Websites, Lists, Projects, Notes, Attachments…
Suggested Courses

590 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question