Trying to create a SQL query

Question

I am trying to create a query that retrieves only the ten companies with the highest number of pickups over the six-month period, this means pickup occasions, and not the number of items picked up. I have done this

It will be useful to provide test data and expected results that we can use? an SQLFiddle would be nice. Good start with the table definitions. — Ryan Vincent
– Ryan Vincent, Commented Nov 10, 2015 at 16:24
Hi, its meant to return two rows. One called company_name (with the name of the company) and the other called pickups (number of pickups the company has completed. — cv28
– cv28, Commented Nov 10, 2015 at 16:25
Sample inserts and expected output (perhaps you could say "I want the top 2 pickups out of these 4 companies") would help us enormously to be able to help you. — Boneist
– Boneist, Commented Nov 10, 2015 at 16:26
I dont get "this means pickup occasions, and not the number of items picked up." this part , what exactly do you want ? — ogres
– ogres, Commented Nov 10, 2015 at 16:26
you should probably look at the Dense_Rank function techonthenet.com/oracle/functions/dense_rank.php — JamieD77
– JamieD77, Commented Nov 10, 2015 at 16:42

JamieD77 · Accepted Answer · 2015-11-10 17:10:49Z

1

SELECT  * 
FROM    customer
JOIN (SELECT manifest.pickup_customer_ref reference,
             DENSE_RANK() OVER (PARTITION BY manifest.pickup_customer_ref ORDER BY COUNT(manifest.trip_id) DESC) rnk
      FROM   manifest 
      INNER JOIN trip ON manifest.trip_id = trip.trip_id
      WHERE trip.departure_date > TRUNC(SYSDATE) - interval '6' month
      GROUP BY manifest.pickup_customer_ref) cm ON customer.reference = cm.reference
WHERE cm.rnk < 11;

this uses dense_rank to determine the order or customers with the highest number of trips first

answered Nov 10, 2015 at 17:10

JamieD77

14k1 gold badge19 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mercyful · Accepted Answer · 2015-11-10 17:00:33Z

0

Hmm well i don't have Oracle so I can't test it 100%, but I believe your looking for something like the following:

Keep in mind that when you use group by, you have to narrow down to the same fields you group by in the select. Hope this helps at least give you an idea of what to look at.

select TOP 10 
    c.company_name,
    m.pickup_customer_ref,
    count(*) as 'count'
from customer c
inner join mainfest m on m.pickup_customer_ref = c.reference
inner join trip t on t.trip_id = m.trip_id
where t.departure_date < DATEADD(month, -6, GETDATE())
group by c.company_name, m.pickup_customer_ref
order by 'count', c.company_name, m.pickup_customer_ref desc

answered Nov 10, 2015 at 17:00

Mercyful

1071 silver badge7 bronze badges

2 Comments

Alex Poole Over a year ago

Oracle doesn't support TOP, DATEADD() or GETDATE(); and column aliases can't be in single quotes...

Mercyful Over a year ago

I knew I hated oracle for a reason! :-) Well there are easy replacements for those if they are looked up, but the gist is still the same.

Collectives™ on Stack Overflow

Trying to create a SQL query

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related