Solved

Make select with only the first occurence?

Posted on 2010-11-28
9
661 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 70

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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle Date 6 41
Trying to understand why my Index is so large 12 52
Call to SQL server times out 5 55
Need definitions 2 17
Creating and Managing Databases with phpMyAdmin in cPanel.
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

739 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