I have a query that I need to show the top 3 within each grouping. I have several distance combinations for Employees and Facilities based on distance between zip codes. I need to get the lowest 3 distances for each employee. (some employees may have only 1 possible record, while others will have more than 5). I have Emp_ID, HomeZip, FacilityZip, Distance --
#11111, Betty, 64015, 64052, 25
#11111, Betty, 64015, 64054, 50
#11111, Betty, 64015, 64057, 75
#11111, Betty, 64015, 65044, 100
#11111, Betty, 64015, 65045, 112
#11111, Betty, 64015, 64050, 120
#22222, Tom, 74015, 74052, 35
#22222, Tom, 74015, 74054, 70
#22222, Tom, 74015, 76057, 75
#22222, Tom, 74015, 75044, 123
#22222, Tom, 74015, 75045, 212
#22222, Tom, 74015, 74050, 320
#22222, Tom, 74015, 48000, 274
... etc.
So I'd get the lowest 3 distances for #11111, Betty and then the lowest 3 for #22222, Tom and so on. Anyone out there able to help me? Thanks!
3  1380  
I have a query that I need to show the top 3 within each grouping. I have several distance combinations for Employees and Facilities based on distance between zip codes. I need to get the lowest 3 distances for each employee. (some employees may have only 1 possible record, while others will have more than 5). I have Emp_ID, HomeZip, FacilityZip, Distance --
#11111, Betty, 64015, 64052, 25
#11111, Betty, 64015, 64054, 50
#11111, Betty, 64015, 64057, 75
#11111, Betty, 64015, 65044, 100
#11111, Betty, 64015, 65045, 112
#11111, Betty, 64015, 64050, 120
#22222, Tom, 74015, 74052, 35
#22222, Tom, 74015, 74054, 70
#22222, Tom, 74015, 76057, 75
#22222, Tom, 74015, 75044, 123
#22222, Tom, 74015, 75045, 212
#22222, Tom, 74015, 74050, 320
#22222, Tom, 74015, 48000, 274
... etc.
So I'd get the lowest 3 distances for #11111, Betty and then the lowest 3 for #22222, Tom and so on. Anyone out there able to help me? Thanks!
Use a subquery to return criteria by selecting the top 3 sorting by ascending distance.
How do I do that? When I try to do a Top 3 query, I get the top 3 for all of the records. I want the top 3 for each grouping. How can I do this in either the query design or sql statement? Any sample code you can provide using this data I have would be much appreciated. Thanks!
No no, you use the Top 3 query in a subquery as part of the WHERE criteria.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Mike Blanchard |
last post by:
I'm new to SQL and databases and have a delema which I'm sure is an
easy thing for you experts out there to do. What I have is this: I
have a table similliar to this:
table name: tablename...
|
by: angelasg |
last post by:
I am working with employee schedules. Each schedule is comprised of
segments (shift, lunch, break, training, etc.) that have rankings.
Each record has the employee id, the date the shift starts,...
|
by: Nip |
last post by:
I am trying to make a database for my test participants. I have 10
participants and have a table with them called participants which
includes an auto number ID and then the participant number and...
|
by: Steve Edwards |
last post by:
Hi,
Given a map:
typedef map<long, string, greater<long> > mapOfFreq;
Is there a quicker way to find the rank (i.e. index) of the elememt that
has the long value of x?
At the moment I'm...
|
by: volunteer |
last post by:
<?xml version="1.0" encoding="UTF-8"?>
<?xml-stylesheet type="text/xsl" href="filename.xsl"?>
<markers date="20060523">
04:21:50 PM
<marker sn="1" rank="6" name="john" />
<marker sn="2" rank="5"...
|
by: codie |
last post by:
I got my code to work be the print out go's down on a angle is there anyway to stop this from happing. P.S sorry for taking up your time
class Card:
suitList =
rankList =
...
|
by: codie |
last post by:
i need to sort a list of card like the rank
sample: ACE of h, ACE of d, ACE of c, ACE of s, 2, 2, 2, 2
this is my code so can anyone help me???
class Card:
suitList =
rankList =
|
by: garyrowell |
last post by:
I have been at this programme for hours trying to work out what is wrong. Any help would be very much appricated. Here is the breif I received.
The program
This week you are going to write three...
|
by: RSethi |
last post by:
I want to rank sales of a business by retail store within each region,state andcity. I want to store the rank in the column rank. How do I do this?
Table structure.
region, state, city,...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
| |
