Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Make select with only the first occurence?

Posted on 2010-11-28
9
Medium Priority
?
663 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 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

721 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