I am working on a query that I would like to get the top 4 values based on a group. Here is the SQL:
SELECT qRenPercRept1.C ountOfccName, qRenPercRept1.M erged, qRenPercRept1.c cName, qRenPercRept1.A vgOfcpUpcomingR en
FROM qRenPercRept1
ORDER BY qRenPercRept1.C ountOfccName DESC;
I want the top 4 Merged based on Countofccname.
is this possible? I've tried a few different things that I have read online, but I keep getting "Data mistype" errors.
Thanks so much in advance!
LSGKelly
7 2059
is this what you want?..... what do you mean by "merged"? -
SELECT TOP 4 qRenPercRept1.CountOfccName, qRenPercRept1.Merged, qRenPercRept1.ccName, qRenPercRept1.AvgOfcpUpcomingRen
-
FROM qRenPercRept1
-
ORDER BY qRenPercRept1.CountOfccName DESC;
-
No, this is not what I need. I have two columns, one has the total amount of carriers [countofccname] and the other is called Merged, which is a field that has a list of Markets. What I need is the top four [countofccname] per Market.
It looks like this:
Atlanta, 32
Atlanta, 30
Atlanta, 25
Atlanta, 6
Atlanta, 1
Baltimore, 34
Baltimore, 32
Baltimore, 3
Baltimore, 2
Baltimore, 1
With Atlanta/Baltimore being the Merged field and the number representing [countofccname]
What I would like is for it to show me only the top four for each market, so my query would only pull the following:
Atlanta, 32
Atlanta, 30
Atlanta, 25
Atlanta, 6
Baltimore, 34
Baltimore, 32
Baltimore, 3
Baltimore, 2
There are also one other field in the query, AvgOfcpUpcoming Ren which needs to be there.
Thanks again for your help!
-
SELECT top 4 qRenPercRept1.Merged, qRenPercRept1.countofccname
-
FROM qRenPercRept1
-
Where qRenPercRept1.Merged = "Atlanta"
-
UNION ALL
-
SELECT top 4 qRenPercRept1.Merged, qRenPercRept1.countofccname
-
FROM qRenPercRept1
-
Where qRenPercRept1.Merged = "Baltimore"
-
ORDER BY qRenPercRept1.Merged, qRenPercRept1.CountOfccName DESC
-
-
I think we are getting closer, except Atlanta and Baltimore are just examples of the names in Merged. There are actually about 30 different Markets.
well you could keep adding unions all down the page to include all 30 markets. Access could run a query like that without breaking a sweat. The only other way is to use VBA to build two recordsets, one like so:
SELECT distinct Merged
FROM qRenPercRept1
this will get your unique markets. store that value in a variable and loop through them while applying that variable into your UNION query recordset.
Thank you so much for your help! I did it the first way. Works like a charm.
:)
Absolute genius...."SELE CT TOP 4" ....i really gotta read more about SQL, bc not knowing that things like that are capable, are really slowing me down i am sure.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: TG |
last post by:
Dear PHP Group,
I have two forms that are used to collect user information. The first one
takes user inputted values such as fullname, city, address etc. I want these
values to display in the second form when it is called. Both forms are .htm
files that call themselves when the submit button is press via the following
command in each form:
<form method="post" action="<?php $server?>">
|
by: Boefje |
last post by:
Hello,
I need to get all records from a table where a for a given playerid no
field enddate exists with value NULL.
table player_team:
id, playerid, startdate, enddate
1, 277, 2003-09-14 00:00:00, NULL
|
by: Zlatko Matić |
last post by:
Hello.
How to reference selected values from a multi-select list box, as a
criteria in a query ?
Is it possible at all?
Regards,
Zlatko
|
by: Preben Randhol |
last post by:
Hi
A short newbie question. I would like to extract some values from a
given text file directly into python variables. Can this be done simply
by either standard library or other libraries? Some pointers where to
get started would be much appreciated.
An example text file:
-----------
Some text that can span some lines.
|
by: Phoe6 |
last post by:
Hi,
I have a configfile, in fact, I am providing a configfile in the
format:
Name: Foo
Author: Bar
Testcases:
tct123
| |
by: Phoe6 |
last post by:
Hi,
Am starting a new thread as I fear the old thread which more than a
week old can go unnoticed.
Sorry for the multiple mails.
I took the approach of Subclassing ConfigParser to support multiline
values without leading white-spaces, but am struct at which position
in _read I should modify to accomodate the non-leading whitespace
based multiline values.
|
by: Rex |
last post by:
Hi
Below is a table with sample data to explain what I want to achieve.
trackID member marker
allele1
------- ------ --------------------------------------------------
-----------------------------------------------------
4734 4577 01-D8S1179 13.5
4734 4577 02-D21S11 12.6
|
by: convexcube |
last post by:
To keep a record of training levels for different tasks, I have 18 option groups with 4 options values each: 0 labelled as "None", 1 labelled as "Trainee", 2 labelled as "Competent" and 3 labelled as "Expert".
I would like to develop a detailed report that will show one employees training levels based on the values of training for each of the 18 categories.
For example, an employee with these values in a table:
Service = 3
Cleaning = 2...
|
by: nassim.bouayad.agha |
last post by:
Hello,
I am seeking for information about conditional XSD validation based on
elements values,but unfortunatly,I could not find a clear answer.Let's
say that I have three XML elements named GROUP,SUB_GROUP and VALUE.How
to validate the VALUE element depending on GROUP and SUB_GROUP values?
Like if GROUP equals 3 and SUB_GROUP equals 4 then VALUE must be a
string enumeration...
Is it possible to perform that with XSD?I have seen some...
|
by: Maric Michaud |
last post by:
Le Thursday 28 August 2008 03:43:16 norseman, vous avez écrit :
Disctionaries are hash tables with a unique key and constant time lookup. What
you want could be implemented as a complex data structures with as many dict
as needed keys, but it seems you really want a relational table and a rdbms.
This is exactly what they are for. A short example with the new python2.5
sqlite package :
|
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 effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
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 protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
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 then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |