Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Make select with only the first occurence?

Posted on 2010-11-28
9
Medium Priority
?
665 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 71

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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone 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

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

926 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