CODEWITHSUNDEEP
sqlms-access
wobblycogs
wobblycogs

Reputation: 4093

Select the first matching row

I have a table "tbl" which looks like this:

prod | cust | qty
p1   | c1   | 5
p1   | c2   | 10
p2   | c1   | 2
p3   | c2   | 8

What I need is a distinct list of product and customer pairs but only the first customer if the product is sold to more than one customer. In order words the results need to look like this:

prod | cust
p1   | c1   
p2   | c1   
p3   | c2

I've tried this every which way I can think of but I can't quite get the correct result. Clearly neither distinct nor group by will work (on their own) since they will both return the p1, c2 row.

I found this question which is a very close match but I can't figure out how to re-write it to get it to do what I need.

To top it all this currently needs to work in Access 2007 or later but at some future point it'll need to work in MySQL as well.

Extra credit to anyone who also joins the results to the customer table so that I can look up the human readable name from the customer code e.g. c1 => Fred Bloggs Spanners

Upvotes: 19

Views: 56513

Answers (4)

David Peden
David Peden

Reputation: 18434

"First" and min() are not the same. If you truly want first, try this:

declare @source table
(
    prod varchar(10),
    cust varchar(10),
    qty int
)

insert into @source (prod, cust, qty) values ('p1', 'c1', 5)
insert into @source (prod, cust, qty) values ('p1', 'c2', 10)
insert into @source (prod, cust, qty) values ('p2', 'c1', 2)
insert into @source (prod, cust, qty) values ('p3', 'c2', 8)

select * from @source

declare @target table
(
    prod varchar(10),
    cust varchar(10),
    qty int
)

insert into @target (prod)
select distinct prod from @source

update @target
set
    cust = s.cust,
    qty = s.qty
from @source s
join @target t on t.prod = s.prod

select * from @target

Upvotes: -1

Mike Ryan
Mike Ryan

Reputation: 4374

I'm going to have to assume that you have some kind of identifier that indicates who is "first". A date column or an identity column or something.

In my example, I've done it with an order_id identify column.

CREATE TABLE products (
    order_id MEDIUMINT NOT NULL AUTO_INCREMENT,
    prod char(2), 
    cust char(2),
    qty int,
    PRIMARY KEY (order_id)
);

INSERT INTO products (prod, cust, qty) VALUES
   ('p1', 'c1', 5),
   ('p1', 'c2', 10),
   ('p2', 'c1', 2),
   ('p3', 'c2', 8);

And then to get your values run:

select p1.prod, p1.cust, p1.qty
from products p1
where not exists (select * from products p2
              where p1.prod = p2.prod
              and p2.order_id < p1.order_id)

where on each line you check to see if there are any other customers that ordered it earlier than you did. If there is an earlier order, then don't list this row. (Thus the not exists)

This is mysql syntax, btw, which you say you're migrating to. (Access experts would have to edit this appropriately.)

Now, if you don't have a column identifying what designates the sequence of when orders were entered, you need one. Any schemes that rely upon implicit row_numbering based upon order of insertion will fall apart eventually, since the "first row" is not guaranteed to remain the same.

Upvotes: 3

J Cooper
J Cooper

Reputation: 4998

Core Question:

SELECT prod, MIN(cust)
FROM yourTable
GROUP BY prod

For the "Bonus":

SELECT T.prod,
       T.cust,
       YC.SomeCustomerAttribute1,
       YC.SomeCustomerAttribute2
FROM (
      SELECT prod, MIN(cust) AS first_cust
      FROM yourProducts
      GROUP BY prod
) AS T
JOIN yourCustomers AS YC ON YC.cust = T.first_cust

Upvotes: 25

JKirchartz
JKirchartz

Reputation: 18022

if you only want the first result add LIMIT 1 to your query, or use SELECT DISTINCT to get unique results.

as for your join check this out:

SELECT * FROM TableA
INNER JOIN TableB
ON TableA.name = TableB.name

this will get all the rows with a matching name from tableA and tableB.

EDIT J Cooper is right, ms-access doesn't have an equivalent to LIMIT. the closest is TOP but I don't think that will help. sorry, haven't used access since college.

Upvotes: 2

Related Questions