INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

top 15 of one field for each department

top 15 of one field for each department

(OP)
I am embarrassed to ask - I just can't figure this dumb question out. I have a table. In that table I have Store, Department, UPC, Description, Retail $. This table has like 15K records in it.
I am trying to write a query that will return the top 5 (sorted descending on Retail $) for each department.

Right now, when I run the query, because it's pulling the top 5 overall, I'm just getting 15 records from one department as shown.

Store Dept UPC Description Retail $    
2032 BAKERY 73314720166 BREAD PUMPERNICKLE BLK $98.67
2032 BAKERY 73314723819 SL PIE 8IN APPLE $89.85
2032 BAKERY 73314723943 BREAD CHEEZY GARLIC $55.86
2032 BAKERY 73314723575 BUN HAMBURGER WHEAT $54.91
2032 BAKERY 73314723823 SL PIE 8IN PUMPKN $53.91

I would like this but also I would like the same data for PRODUCE, GROCERY, DAIRY, DELI etc.

The sql suntax appears as follows:

SELECT TOP 5 shrink.Store, shrink.Dept, shrink.UPC, shrink.Description, shrink.[Retail $    ]
FROM shrink
WHERE (((shrink.Store)=2032))
ORDER BY shrink.Dept, shrink.[Retail $    ] DESC;

Can this be done without running a seperate query for each department? Thanks!


RE: top 15 of one field for each department

This isn't a typical query. I usually use a subquery in the criteria that relies on having a primary key field in the table. Do you have one? What do you want to happen if there are ties?

Also please use Pre and Code tags when posting columns of numbers and code so they can be read more easily.

 Store  Dept    UPC          Description              Retail $    
 2032   BAKERY  73314720166  BREAD PUMPERNICKLE BLK   $98.67
 2032   BAKERY  73314723819  SL PIE 8IN APPLE         $89.85
 2032   BAKERY  73314723943  BREAD CHEEZY GARLIC      $55.86
 2032   BAKERY  73314723575  BUN HAMBURGER WHEAT      $54.91
 2032   BAKERY  73314723823  SL PIE 8IN PUMPKN        $53.91
 


CODE --> SQL

SELECT TOP 5 shrink.Store, shrink.Dept, shrink.UPC, shrink.Description, shrink.[Retail $    ]
 FROM shrink
 WHERE (((shrink.Store)=2032))
 ORDER BY shrink.Dept, shrink.[Retail $    ] DESC; 

Duane
Hook'D on Access
MS Access MVP

RE: top 15 of one field for each department

(OP)
No Sir - No primary Key. Also, I hadn't considered the tie situation. Perhaps I need to consider a different solution.

RE: top 15 of one field for each department

This is an example of the 3 most recent orders for each customer in the Northwind sample database:

CODE --> SQL

SELECT Orders.CustomerID, Orders.OrderDate, Orders.OrderID
FROM Orders
WHERE Orders.OrderID In 
(SELECT TOP 3 OrderID
 FROM Orders O
 WHERE O.CustomerID = Orders.CustomerID
 ORDER BY OrderDate Desc)
ORDER BY Orders.CustomerID, Orders.OrderDate DESC; 

Duane
Hook'D on Access
MS Access MVP

RE: top 15 of one field for each department

(OP)
Thanks Duane! I think that'll work nicely.

RE: top 15 of one field for each department

What about this ?

CODE --> SQL

SELECT A.Store, A.Dept, A.UPC, A.Description, A.[Retail $    ]
FROM shrink A
INNER JOIN shrink B ON A.Store=B.Store AND A.Dept=B.Dept AND A.[Retail $    ]<=B.[Retail $    ]
WHERE A.Store=2032
GROUP BY A.Store, A.Dept, A.UPC, A.Description, A.[Retail $    ]
HAVING Count(*)<=5
ORDER BY 1, 2, 5 DESC 

Hope This Helps, PH.
FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close