Rank query help

Below is the SQL for a query that I have. Below the SQL is the results. What
I need to do is rank the score by each name in descending order. When the
name changes I need the rank to start at 1 again.   

Thanks in advance for your help. I am stuck.


SELECT [Plant].Name, [Plant].DefectCode, [Plant].Score
FROM [Plant]
GROUP BY [Plant].Name, [Plant].DefectCode, [Plant].Score
ORDER BY [Plant].Name, [Plant].Score DESC;



Name			DefectCode	Score	
Plant A 2000 Widget 	100		8
Plant A 2000 Widget	105		5
Plant A 2000 Widget	106		4
Plant A 2001 Widget	105		4
Plant A 2001 Widget	102		3
Plant A 2001 widget	157		2
Plant B 2000 Widget	105		10
Plant B 2000 widget	100		8
Plant B 2000 widget	120		3
Plant B 2001 widget	100		11
Plant B 2001 widget	110		10
Plant B 2001 widget	107		7
Plant C 1999 widget	100		8
Plant C 1999 widget	110		5
Plant C 1999 widget	120		4
Plant C 2004 widget	111		5
Plant C 2004 widget	111		5
Plant C 2004 widget	111		5

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200711/1

0
weazer
11/6/2007 9:52:16 PM
access.queries 6343 articles. 1 followers. Follow

3 Replies
336 Views

Similar Articles

[PageSpeed] 36

Try this --
SELECT Q.Name, Q.DefectCode, Q.Score, (SELECT COUNT(*) FROM Plant Q1
      WHERE Q1.[Name] = Q.[Name]
        AND Q1.Score > Q.Score)+1 AS Rank
FROM Plant AS Q
ORDER BY Q.Name, Q.Score DESC;

-- 
KARL DEWEY
Build a little - Test a little


"weazer via AccessMonster.com" wrote:

> Below is the SQL for a query that I have. Below the SQL is the results. What
> I need to do is rank the score by each name in descending order. When the
> name changes I need the rank to start at 1 again.   
> 
> Thanks in advance for your help. I am stuck.
> 
> 
> SELECT [Plant].Name, [Plant].DefectCode, [Plant].Score
> FROM [Plant]
> GROUP BY [Plant].Name, [Plant].DefectCode, [Plant].Score
> ORDER BY [Plant].Name, [Plant].Score DESC;
> 
> 
> 
> Name			DefectCode	Score	
> Plant A 2000 Widget 	100		8
> Plant A 2000 Widget	105		5
> Plant A 2000 Widget	106		4
> Plant A 2001 Widget	105		4
> Plant A 2001 Widget	102		3
> Plant A 2001 widget	157		2
> Plant B 2000 Widget	105		10
> Plant B 2000 widget	100		8
> Plant B 2000 widget	120		3
> Plant B 2001 widget	100		11
> Plant B 2001 widget	110		10
> Plant B 2001 widget	107		7
> Plant C 1999 widget	100		8
> Plant C 1999 widget	110		5
> Plant C 1999 widget	120		4
> Plant C 2004 widget	111		5
> Plant C 2004 widget	111		5
> Plant C 2004 widget	111		5
> 
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200711/1
> 
> 
0
Utf
11/6/2007 11:50:01 PM
Thanks for the reply. When I ran this SQL it gave me a syntax error with the
following:

>SELECT Q.Name, Q.DefectCode, Q.Score, (SELECT COUNT(*) FROM Plant Q1
>      WHERE Q1.[Name] = Q.[Name]
>        AND Q1.Score > Q.Score)+1 

Everything I tried did not correct the error.

Please le me know if you see where the syntax error is.

Thanks again.

KARL DEWEY wrote:
>Try this --
>SELECT Q.Name, Q.DefectCode, Q.Score, (SELECT COUNT(*) FROM Plant Q1
>      WHERE Q1.[Name] = Q.[Name]
>        AND Q1.Score > Q.Score)+1 AS Rank
>FROM Plant AS Q
>ORDER BY Q.Name, Q.Score DESC;
>
>> Below is the SQL for a query that I have. Below the SQL is the results. What
>> I need to do is rank the score by each name in descending order. When the
>[quoted text clipped - 26 lines]
>> Plant C 2004 widget	111		5
>> Plant C 2004 widget	111		5

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200711/1

0
weazer
11/8/2007 12:51:25 AM
On Nov 7, 7:51 pm, "weazer via AccessMonster.com" <u20322@uwe> wrote:
> Thanks for the reply. When I ran this SQL it gave me a syntax error with the
> following:
>
> >SELECT Q.Name, Q.DefectCode, Q.Score, (SELECT COUNT(*) FROM Plant Q1
> >      WHERE Q1.[Name] = Q.[Name]
> >        AND Q1.Score > Q.Score)+1
>
> Everything I tried did not correct the error.
>
> Please le me know if you see where the syntax error is.
>
> Thanks again.
>
> KARL DEWEY wrote:
> >Try this --
> >SELECT Q.Name, Q.DefectCode, Q.Score, (SELECT COUNT(*) FROM Plant Q1
> >      WHERE Q1.[Name] = Q.[Name]
> >        AND Q1.Score > Q.Score)+1 AS Rank
> >FROM Plant AS Q
> >ORDER BY Q.Name, Q.Score DESC;
>
> >> Below is the SQL for a query that I have. Below the SQL is the results. What
> >> I need to do is rank the score by each name in descending order. When the
> >[quoted text clipped - 26 lines]
> >> Plant C 2004 widget    111             5
> >> Plant C 2004 widget    111             5
>
> --
> Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200711/1

Prolly needs a closing paren before "AS Rank":

SELECT Q.Name, Q.DefectCode, Q.Score, (SELECT COUNT(*) FROM Plant Q1
      WHERE Q1.[Name] = Q.[Name]
        AND Q1.Score > Q.Score)+1 ) AS Rank
FROM Plant AS Q
ORDER BY Q.Name, Q.Score DESC;

0
Smartin
11/10/2007 10:58:03 PM
Reply:

Similar Artilces:

help with database design required
Hello Everyone, I have a system that has to track the movement of tools from in and out of the stores. Borrower Details: tblname: id-PK,name,department tbltoolmaster ToolID-PK Toolno ToolDesc Location tbltooltransaction Tranid-PK TrantoolID - FK NameID - FK DateTaken DateReturned what i am attempting to do is for every tool borrowed from the tool store i need to be able to record the date taken and date returned. If the date returned is not null - Tool is Available Once the tool is burrowed by a person A on a given date than the status should be updated to loaned and when he returns ...

Help With Formula #5
I have data in a worksheet A B C 1 Verify 2% 99% 2 Audit 2% 98% 3 Account 5% 97% 4 Forward 4% 96% 5 Place 5% 95% I have another cell (F1) in the sheet that i would like to list the text in A1, but only if B1 is at least 5% if not then I want it to list A2, but only if B2 is at least 5% and so on down to A5. Please Help. What happens with row 3 and row 5? -- Don Guillett SalesAid Software donaldb@281.com "Report Lackey" <anonymous@discussions.microsoft.com> wrote in message news:033b01c3d62d$ebd800e0$a401280a@phx....

Linq select from datatable help
Hi, I have a datatable with your typical ID column and a notes column. I'm trying to get the notes for a specific id passed to my method. Lanquage: C# datatable is dtConfig iID is passed into the method Here is my (non-working :) ) linq code: var sNotes = (from row in dtConfig.AsEnumerable() where row.Field<int>("ID") == iID select row.Field<String>("Notes")); What am I doing wrong? PS: I'd like to return a string rather then a var if possible, ie: string sNotes = .... Thanks. G Nevermind, I found the issue. I needed to add...

HELP! X.400 Two Organizations Same SMTP
We are doing a migration from 5.5 to 2k3 and are having issues. We have two organizations which have the same smtp address, different X.400 addresses. The 5.5 is a hub and spoke configuration, where the IMC is on a hub, and the ADC and X.400 connect to a spoke. Mail routes from 2k3 to 5.5 without issue, and from 5.5 to 2k3 ONLY if the x.400 address is listed in the general tab in email. Otherwise, messages get forwarded to out IMC, which is set for the SMTP to be inbound only, and the mail loops until it is NDR'ed. The ADC automatically places an SMTP address there, and I don't wa...

Query Based Distribution Lists #3
I can create QBDL based on STATE, ZIP, LASTNAME and many other fields. I would like to make a list based on Group Membership. There is a field under USER called MEMBER OF but it does not give me any results when I do a preview. I have many employees who work in multiple locations and need to belong to several different groups even though they have only one login and work out of a single office with the same address etc. So I made them members of groups based on the different location in which they work and wanted to send mail based on this membership. McMurray <McMurray@disc...

Help with Shared Calendars and contacts in Entourage
Hi ive recently set-up my exchange server on PC and have easily set-up my PC's account. I have set-up my Mac accounts but have one problem. I cant get access to any shared calendars or address books through entourage 2004. Does anyone know what im doing wrong? Any help is a plus. Thank you. James. You probably ought to post this to an Entourage newsgroup. -- Ed Crowley MVP - Exchange "Protecting the world from PSTs and brick backups!" "Mac Station" <macstation@sasdesign.co.uk> wrote in message news:BF3CE795.3CA%macstation@sasdesign.co.uk... > Hi ive...

Using single button to run multiple query
I want to use single button on the form to run multiple queries. How it can be done? Where should I write queries and code? One way to do this would be to create your queries, then use the command button to run code that calls each query. Another approach would be to create all your queries, then create a macro that runs all your queries, and call that macro using code behind a command button. -- Regards Jeff Boyce www.InformationFutures.net Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ "Gajanan&qu...

Help requested in formulating Functions and script to create a mat
Visual Studio.net 2003, SQL Server Report Designer, SQL Query Analyser. I have a table of milestones with data such as project, miletone name, baseline date, planned date and actual date. My task is to produce a chart that has months in the columns and for each month a set of blocks of data, made by concatenating fileds form the table, for milestones due in that month. I can extract the 'blocks' of data easily enough but when I put them in columns each successive month starts on a new line below those of the previous month. I need it so that the first milestone of each ...

Internal error.. any help?
Can anybody please help me in fixing this link error that i get in my VC++ project? Linking... LINK : error : Internal error during ReadSymbolTable ExceptionCode = C0000005 ExceptionFlags = 00000000 ExceptionAddress = 0040ED6B NumberParameters = 00000002 ExceptionInformation[ 0] = 00000001 ExceptionInformation[ 1] = 00000000 CONTEXT: Eax = 40070F98 Esp = 0012EAC4 Ebx = 3FFF0000 Ebp = 004695A8 Ecx = C0000A40 Esi = 40070F5C Edx = 00000000 Edi = 3FFF01C0 Eip = 0040ED6B EFlags = 00010202 SegCs = 0000001B...

Help with relations
Hello. I have quite a problem with relations, imagine a table Clients and a table Products. if i relate them one to one... i would have the table Products with a list of products, price, quantity, date, and a yes/no object called Buy(to return results on a query for reports) the problem is with date... how can i relate or create fields and make this work..the way i could know when a certain product was bought, like keeping a history of every purchase of every client... I just cannot figure it out ... could anyone help.. Thanks. K, The classic order entry scenario is shown in the sample dat...

Force data type from Text to Memo in a simple Make Table Query
I'm concatenating fields of various data types that upon completion sometimes reaches around 500 characters. Not huge, but larger than the Text limitation to which is what Access 2007 of course converts this. How can I force the data type to be Memo while I'm in the query so the resulting table displays all the data without any truncation. -- TIA Eric S UPRR I don't believe you can. I think you'll have to create the table first, and then append to it. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "E...

Help! How do I restore a file?
I accidentally saved a new file with the same name as an existing one, and clicked 'override', forgetting they were in the same location. How do I restore the original file that I accidentally overrode? Thank you! On Tue, 1 Dec 2009 20:48:01 -0800, A Person <A Person@discussions.microsoft.com> wrote: I'm not sure this is a question about Microsoft Access, the topic of this newsgroup. Anyway, you have to restore the previous version from a backup you made previously. The exact procedure will be documented in the help file for your backup software. -Tom. Micro...

Need help with the query.
How can I get desire results below. I cannot seem to figure this out. Any help is greatly appreciate. IF OBJECT_ID('Tempdb.dbo.#Temp', 'u') IS NOT NULL DROP TABLE #Temp GO CREATE TABLE #Temp ( UserHistoryId INT NULL, UserId VARCHAR(6) NULL, EventDate DATETIME NULL ) GO INSERT INTO dbo.#Temp([UserHistoryID],[UserId],[EventDate])VALUES (664, 'User1', CONVERT(DATETIME, 0x00008eac00000000)) INSERT INTO dbo.#Temp([UserHistoryID],[UserId],[EventDate])VALUES (1014, 'User1', CONVERT(DATETIME, 0x00009c2600ef152a)...

Totals Query Help!
Hi, I have a table with the following fields: Manufacturer Model Value SoldMonth What I'm trying to do is write a query that will give me an average Value for each month, but I want the average to be based only on records where the Model is consistent across all months. So if Model x is missing from one or more months all records for that model would be excluded from all the overall Monthly averages. So the query will only average records where the Model is present in each month. Any help with this would be greatly appreciated. Regards.....Jason It'll help us if you can ...

Budget Probelem
In Money 2005 Standard, I have both credit card account and normal cheque account. How do I budget for making payments to my credit card from my cheque account? If I categorise the payment as a transfer between accounts, I can't budget for this item. If I categorise this item as a credit card payment the other side, on the credit card account, gets categorised as income, which in artificially increasing my income. Can any one help? Budget is about getting richer or poorer, not where your cash goes. (Forecast Cash Flow is all about that.) When you charge something on the card you ge...

Pivot table help I think !
Hi All, I have a report I need to create which goes as follows. I have a data list of around 56k records. The rows contain this; category 1, category 2, category 3, call id and Date I've created a pivot table with Cat1, cat2, cat 3 on the row area, Count of Call ID in the data area and date in the column area (this is grouped by Month). I'm looking at the months of Mar, Apr and May. I want to sort it, in descending order, by the difference of 'Count of Call ID' there is between Mar and May. If I cannot perform the calculation in the pivot table, is there a way of ungroupi...

Help with Lookup/Sum Formula
I have a spreedsheet with this type of data, for example Product Group Quantity Size 2812 10 0.75 2812 100 0.75 2812 300 0.5 2817 100 0.25 2817 200 1 2845 1000 1.5 I want to be able to set up a formula that you enter a product grou (2812) in a cell and it will for that product group go and sum up al the quantities for each size. The formula would go in the Sum colum For Example ...

need help with KB article Q315407.
I need to use this registry tweak to optimize our memory usage on Exch2003 on Server2003. We now have 2 GB memory and the system is rebooting or blue-screening after 8 hours or so. The problem is that the article says to click Edit > Add Value in the Registry Editor, but the only options available are Edit > New > String Value, Binary Value, Word Value, etc.. Which Value do I create ? ? Thanks. dword "chrism" <chris@no_spam> wrote in message news:O$3C$IVhEHA.904@TK2MSFTNGP09.phx.gbl... >I need to use this registry tweak to optimize our memory usage on Ex...

List Box
I am stumped and need some assistance - PLEASE. New to List Boxes. I have created two unbound list boxes on an unbound form to use as criteria for a report. Both list boxes (lboxCategory and lboxSupplier) have two fields CategoryID and Category and SupplierID and Supplier sorting ascending on latter fields. The Multi-Select property is set to Extended. I have a command button cmdPreview that I would like to use to preview the report after selections are made. I will select two or more choices from each list box at a time. Can anyone get me started with the code I need to make this h...

Microsoft Query #8
I'm using Excel to query an oracle database. When I go to Microsoft Query to edit my query, I can't view my criteria or tables. I click on the view menu and am unable to select view "criteria" or "tables". Thus, all that shows up in the screen is the data in my query. This has happend to me before. Can anybody help me? I was able to answer my own question. It seems when query is complicated enough, you are unable to view the criteria, since you are now using SQL to query the data. It seems that if you click on the SQL button and scroll down, delete th...

Help #6
I am a student I Niagera college and am working with excel, I have a n ifstatmet that is giving the correct answer but its not solving the anser. The statment : =IF(G9>B3,+"sum(G9*C3)",+"sum(G9*1)") The Answer sum(G9*C3) The problemi is that it is the wright answer but it will not tally the sum. Help me PLZ How about: =IF(G9>B3,G9*C3,G9) the use of the SUM function is wastefull (as there is nothing to sum), as it is the multiplication by 1. Regards, KL "Niagera College Student" <Niagera College Student@discussions.microsoft.com> wrot...

Help with SQL- SupplierList cost into Item cost field
Hi, re-posted! See below. Hi, thanks for that- it worked perfectly. There is just one snag however and I didn't think of it until I was testing. Many of the suppliers use specific currencies so the information I need copied from the Supplier tab is actually the Local Cost. When I view the SupplierList table, Local Cost doesn't appear as a column. Is it just a calculation based on exchange rates for information or does it actually exist somewhere within the database? Your help once again would be much appreciated. T. "convoluted" wrote: > Hi Tara - backup your...

Worksheet Help req PLZ
Hey All, I have a small issue where I have created a worksheet called data and in Cell A3 I have a linked cell to another worksheet and cell "WK48!A2" In Cell A4 in the "data" worksheet I need a link to "WK49!A2". I would like to link all cells in Column A to consecutively "WKxx" worksheet once I add them to the workbook. I thought I could drag them mouse down but al I get is links to consecutive cells in "WK48" work sheet. Please help mag()() Try =INDIRECT("'WK"&ROW(48:48)&"'!A2") -- Reg...

HELP! Microsoft.Crm.Platform.Proxy is not intended for direct use, please use the public SDK.
Can someone post the following DLL's from CRM 1.2? They should be on the installation CD. If you want to e-mail send to robert[at] ockhamgroup.com Microsoft.Crm.Platform.Proxy.dll (1.2.3297.0) Microsoft.Crm.Platform.Types.dll (1.2.3297.0) Details... I'm attempting the following solution: Moving External Data into Microsoft CRM: Lead Generation http://msdn2.microsoft.com/en-us/library/ms913871.aspx http://download.microsoft.com/download/e/4/5/e45c4994-6878-4871- 8699-6793b8f13c34/leadgeneration.exe What I need is either: 1. a working compiled LeadGenerator.exe executable; o...

Can anyone help with this chart?
I would like to have the following values/labels on the y and x axis of any style of chart: X-axis: July, August, September etc for a full financial year; Y-axis: FB, AP, DR, LA, PC. OR the other way around. Presently the Y-axis simply displays the labels 'Jan Jan Jan Jan Jan' etc. Thank you. Any help would be much appreciated. Check out Jon Peltier's article on creating a vertical category axis: http://peltiertech.com/Excel/Charts/Y_CategoryAxis.html -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "A little stuck" wro...