Solved

How Can I Sequentially Number the Results (Rows) of a Query?

Posted on 2008-06-24
42
397 Views
Last Modified: 2013-11-27
I have a query that summarizes the quantities of defects in a list of categories and Sorts them in Descending Order, (for a Pareto) so the results look like the following:

Defect           Qty
Off_Color      50
Warped         40
Broken          30

What I am looking for is a Rank, so that the results would look like

Defect           Qty      Rank
Off_Color      50           1
Warped         40           2
Broken          30           3

Can anyone explain the SQL for doing that? Thanks.
0
Comment
Question by:Rex85
  • 17
  • 10
  • 8
  • +2
42 Comments
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 21858632
In Access?

There's a very cool method if you're using SQL Server 2005 ... but in Access it would be a variant of what I offered here:

http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_23226926.html

If you like, I'll adapt the code for the situation you've shown.
0
 

Author Comment

by:Rex85
ID: 21858685
Yes. It is in Access. If you could adapt it, that would be great. I took a look at it, and it is beyond my current capabilites to change stuff like that wtihout seriously screwing it up.
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 21858712
OK, what is your table name?  And are those the only columns from it that you need?
0
 

Author Comment

by:Rex85
ID: 21858754
the table name would be FifteenthStWoodFPYParetoTable and yes, Defect and Qty are the only two columns in this table. Thanks very much.

Rex
0
 
LVL 2

Expert Comment

by:mriz81
ID: 21858866
It will be something like


select * , 

(select count(Defect)

from YourTableName t1

where t1.QTY < = t2.QTY) as Rank,
 

from YourTableName t2

order by t2.QTY

Open in new window

0
 
LVL 44

Expert Comment

by:GRayL
ID: 21859482
You should qualify all the fields including * in the Select clauses with the table aliases.
0
 
LVL 2

Expert Comment

by:mriz81
ID: 21859603
I think GRayL is correct. We need to slightly change the query like
select t2.Defect, t2.QTY, ( Select count(Defect) from FifteenthStWoodFPYParetoTable t1 where t1.QTY < = t2.QTY ) as Rank from FifteenthStWoodFPYParetoTable t2 order by t2.QTY

Open in new window

0
 
LVL 44

Expert Comment

by:GRayL
ID: 21859643
Don't forget Select Count(t1.Defect)  in the SubQuery
0
 

Author Comment

by:Rex85
ID: 21901683
Guys:

I have been struggling to adapt this to my fileds etc...

Can you tell me what t1 and t2 represent? I thought they were tables, but there is only one table.

Thanks

Rex
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 21901715
They are 2 different aliases to the same table.

That is needed w/ any self-join or subselect on the same table.
0
 

Author Comment

by:Rex85
ID: 21901926
I get a Syntax error when tring to save this. I seem to always get these with subqueries even though I copy them exactly as posted. (See attached Screen Shot)
Syntax-error.bmp
0
 
LVL 44

Expert Comment

by:GRayL
ID: 21902414
That should be (SELECT Count(t1.Defect) FROM...
---------------------------------------^--this alias was omitted.
0
 

Author Comment

by:Rex85
ID: 21902469
( SELECT Count(t1.Field20) FROM  KO_QN_Data t1 WHERE  t1.QTY < = t2.QTY )

is what I have, and it's what hangs up in the sytax (Field20 is the table field name with no alias)
0
 
LVL 44

Expert Comment

by:GRayL
ID: 21902778
In http:#a21858754, you said Defect and Qty were the only two fields in table FifteenthStWoodFPYParetoTable.  Now we are seeing Field20 in table KO_QN_Data.  Where is this going?
0
 
LVL 2

Expert Comment

by:mriz81
ID: 21902795
Please use this subquery
( SELECT Count(t1.Field20) FROM  KO_QN_Data t1 WHERE  t1.QTY >= t2.QTY )

Open in new window

0
 
LVL 44

Expert Comment

by:GRayL
ID: 21903010
You cannot have t2 as an alias for a different table name.  What does the total query now look like?
0
 

Author Comment

by:Rex85
ID: 21903200
GRayL:
RE: In http:#a21858754, you said Defect and Qty were the only two fields in table FifteenthStWoodFPYParetoTable.  Now we are seeing Field20 in table KO_QN_Data.  Where is this going?

I have two different data sets, but I need to do the same thing with both. I have had so many problems with this, (Including erasing the table I was originally trying to achieve this with) i can't remember them all, but this data set (KO_QN_Data) seemed to fit better with the query language you all were suggesting anyway.

Regardless, I can't get past sytax errors to actually save a query, so I can't show you what the query looks like. Field20 is the same as Defect. It's just in a different table, that I can not rename.
0
 

Author Comment

by:Rex85
ID: 21903222
mriz81: I tried

( SELECT Count(t1.Field20) FROM  KO_QN_Data t1 WHERE  t1.QTY >= t2.QTY )
 
it moved the sytax error to the next statement, but I still couldn't save it.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 21903244
Then the new table is KO_QN_Data, and the two fields are Field20 (Defect) and ?? (Qty)
0
 

Author Comment

by:Rex85
ID: 21903251
Yes. That is correct.
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 44

Expert Comment

by:GRayL
ID: 21903277
Is Qty the same name in the new table?  If so, then:

SELECT t2.Field20, t2.QTY, ( SELECT Count(t1.Field20) FROM KO_QN_Data t1 WHERE t1.QTY < = t2.QTY ) as Rank FROM KO_QN_Data t2 order by t2.QTY
0
 

Author Comment

by:Rex85
ID: 21903388
I forgot I was aliasing DefectQty (ext) As Qty.

with that caveat, I have pasted in the code section below, the entire query.

However, when trying to save, it gives me the syntax error and higlights the first part of the subquery as shown in the attached
SELECT t2.Field20, t2.[DefectQty (ext)],  (SELECT Count(t1.Field20) FROM KO_QN_Data t1 WHERE t1.[DefectQty (ext)] < = t2.[DefectQty (ext)] ) as Rank FROM KO_QN_Data t2 order by t2.[DefectQty (ext)]

FROM KO_QN_Data

Open in new window

Syntax-Error.jpg
0
 
LVL 2

Expert Comment

by:mriz81
ID: 21904766
This should work for you
SELECT t2.Field20, t2.[DefectQty (ext)],  ( SELECT Count(t1.Field20) FROM KO_QN_Data t1 WHERE t1.[DefectQty (ext)] >= t2.[DefectQty (ext)] ) as Rank FROM KO_QN_Data t2 order by t2.[DefectQty (ext)] desc

FROM KO_QN_Data

Open in new window

0
 
LVL 2

Expert Comment

by:mriz81
ID: 21904769
Please ignore last query .. try this one
SELECT t2.Field20, t2.[DefectQty (ext)],  ( SELECT Count(t1.Field20) FROM KO_QN_Data t1 WHERE t1.[DefectQty (ext)] >= t2.[DefectQty (ext)] ) as Rank FROM KO_QN_Data t2 order by t2.[DefectQty (ext)] desc

Open in new window

0
 
LVL 2

Expert Comment

by:mriz81
ID: 21904772
And If you are still not able to run this query ... please attach your database file with new post, so that I can test it and suggest a final query.

Thanks

--
- R i z
www.PDFonFly.com
0
 

Author Comment

by:Rex85
ID: 21907287
mriz81:

Your query worked great as is. I am thrilled with it.

However, when I tried to add back in the WHERE clause I use to narrow the records for a particular facliity, it ruined the way the Rank column you had set up worked. (It gave huge numbers, the first was something like 2512) I am new to Access and SQL ( a few months) but subqueries in particular really are baffling me.

That feels like it might be "scope creep" so, should I award the points on this one and open up that other issue as a separate question?

Thanks

Rex
0
 
LVL 44

Expert Comment

by:GRayL
ID: 21909966
Rex did you try my last post?
0
 

Author Comment

by:Rex85
ID: 21910152
GRayL: I tried your post of 06.30.2008 at 05:45PM EDT, ID: 21903277, but I couldn't save or run it due to a syntax error.

Is that the one to which you are referring?

I described that in my post of 06.30.2008 at 05:59PM EDT, ID: 21903388

Does that answer you?

0
 
LVL 2

Expert Comment

by:mriz81
ID: 21911086
Rex, As your original question is resolved so I am sure I deserve the points :).
You can post a new question for further changes you need in it - OR - post the latest query in reply and I'll see if it needs small fix or a bit longer look.

Thanks

--
- R i z
www.PDFonFly.com
0
 

Author Comment

by:Rex85
ID: 21911573
Below is the query with the WHERE clause I need. I have another piece of this I need to add around concatenating some fields, so it might be best if I just posted it as a new question. Let me know please, and thanks very much for your help. I really appreciate it.

Rex
SELECT t2.Field20, t2.[DefectQty (ext)], ( SELECT Count(t1.Field20) FROM KO_QN_Data t1 WHERE t1.[DefectQty (ext)] >= t2.[DefectQty (ext)] ) AS Rank

FROM KO_QN_Data AS t2

WHERE GetWeekEndingDate([Created on])>=Date()-90 AND (t2.[Name 1]="KO-Borden" OR t2.[Name 1]="flexcel-Borden") AND GetBucket([Code group])="Product" AND Clng(TransformTextFields(t2.[DefectQty (ext)]))>0

ORDER BY t2.[DefectQty (ext)] DESC;

Open in new window

0
 
LVL 44

Expert Comment

by:GRayL
ID: 21913311
When you want to reference a previous post in the same thread after having typed something into the Post Comment/Soution dialog box, scroll up to the previous post, highlight the ID (numbers only), press Ctl+C, scroll back down to your Post Comment box,  type 'http#a' and press Ctl+V.  Presto, you have created a html tag that will take you back to the point of reference.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 21913337
This doesn't need another question, only a frank explanation of what is needed.  Looking at the original question and the dialog that has gone on to get here, somewhere along the line we got off the path.  

Rex85, care to summarize?
0
 
LVL 2

Expert Comment

by:mriz81
ID: 21913687
Try the query below. If it does not work, please attach your database with your reply.

Thanks

--
- R i z
www.PDFonFly.com
SELECT t2.Field20, t2.[DefectQty (ext)], ( SELECT Count(t1.Field20) FROM KO_QN_Data t1 WHERE t1.[DefectQty (ext)] >= t2.[DefectQty (ext)] and GetWeekEndingDate(t2.[Created on])>=Date()-90 AND (t2.[Name 1]="KO-Borden" OR t2.[Name 1]="flexcel-Borden") AND GetBucket(t2.[Code group])="Product" AND Clng(TransformTextFields(t2.[DefectQty (ext)]))>0 ) AS Rank

FROM KO_QN_Data AS t2

WHERE GetWeekEndingDate(t2.[Created on])>=Date()-90 AND (t2.[Name 1]="KO-Borden" OR t2.[Name 1]="flexcel-Borden") AND GetBucket(t2.[Code group])="Product" AND Clng(TransformTextFields(t2.[DefectQty (ext)]))>0

ORDER BY t2.[DefectQty (ext)] DESC;

Open in new window

0
 

Author Comment

by:Rex85
ID: 21915933
mriz81: that did not work. I am attaching a pared down version.

There are four queries here. Borden_QN_Pareto_qry is the one I use to generate the Pareto. That was the baseline query. I wanted to add a rank column to it, so that I could do some things with the "Top 5" and show position shifts.

The query you provided that works is Borden_QN_Pareto_qry_test_IT_WORKS. It works, but does not have the WHERE clause I need to isolate to the Borden data. That's what I am trying to do. I have to make one of these for each facility. Borden is one of the facilities. Once I get that straight, I can replicate for the others.

Regarding the concatenation. You will see that in the Borden_QN_Pareto_qry. It is that concatenation and the WHERE clause that I am having trouble working with when it comes to the query.

Does that explain it?

End result should be a Pareto, with a rank column, and the query should be able to isolate data to a specific facility and of a specific type (the GETBUCKET) function, back to a certain number of days.


KO-Quality-Master-2008-Linked-20.mdb
0
 
LVL 16

Expert Comment

by:Rick_Rickards
ID: 22047991
You're very close.  Credit to those who came before me who got you so very close to the solution.  A few final tweaks unqiue to your circumstance and you're there.

Issue #1: Field20 and Defect Qty appears more than once in your Table.  To resolve that issue we need a source query to remove the duplicates (qryDefects)

SELECT DISTINCT KO_QN_Data.Field20, KO_QN_Data.[DefectQty (ext)]
FROM KO_QN_Data
ORDER BY KO_QN_Data.[DefectQty (ext)] DESC , KO_QN_Data.Field20;

Issue #2: There are rows where the Defect qty and value in Field 20 appear more than once.  To give everyone a unique rank we need to sub sort the Defect Qty Rows so that one will always appear before the other in a predictable way.  Hence...

ORDER BY KO_QN_Data.[DefectQty (ext)] DESC , KO_QN_Data.Field20;

Issue #3: Having removed duplicate values for both fields and insured that rows with the same defect qty will sub sort by Field20 we now need the duplicate rows to inherit unique rank's.  A 2nd Sub Query to identify how many duplicates came before and subtract out that number to your Rank column is needed.  Hence...

SELECT Count(*) FROM qryDefects t1 WHERE t1.[DefectQty (ext)] = t2.[DefectQty (ext)] AND  t1.[Field20] > t2.[Field20])

The final product included in the application below is a query named "Borden_QN_Pareto_qry_test_IT_WORKS2".  The full query looks like this...


SELECT t2.Field20, t2.[DefectQty (ext)],
(SELECT Count(*) FROM qryDefects t1 WHERE t1.[DefectQty (ext)] >= t2.[DefectQty (ext)] ) -
(SELECT Count(*) FROM qryDefects t1 WHERE t1.[DefectQty (ext)] = t2.[DefectQty (ext)] AND  t1.[Field20] > t2.[Field20]) AS Rank
FROM qryDefects AS t2
ORDER BY t2.[DefectQty (ext)] DESC , t2.Field20;

To keep it simple I've attached an updated copy of your application below.
KO-Quality-Master-2008-Linked-20.mdb
0
 
LVL 16

Expert Comment

by:Rick_Rickards
ID: 22064789
Hey Rex85,

Did This work for you?

Rick
0
 

Author Comment

by:Rex85
ID: 22070326
Sorry. I have not had a chance to try it yet. (Unexpected Hospital trip)

I am going to work on it this afternoon. Thanks.
0
 

Author Comment

by:Rex85
ID: 22072393
Rick:
Thank you. It worked great. However, I am still having trouble with the WHERE clause. I need to following WHERE clause to isolate this Pareto to only a the last 90 days of data, only a certain facility (Borden) and only a given type (the GetBucket Function that resolves to Product)

When I tried pasting that in your query and tried to run it,  it prompted me to enter a "Created on" value.


WHERE GetWeekEndingDate([Created on])>=Date()-90 AND (KO_QN_Data.[Name 1]="KO-Borden" OR KO_QN_Data.[Name 1]="flexcel-Borden") AND GetBucket([Code group])="Product" AND Clng(TransformTextFields([KO_QN_Data.DefectQty (ext)]))>0

Open in new window

0
 
LVL 16

Accepted Solution

by:
Rick_Rickards earned 500 total points
ID: 22075777
The update needs to be made to qryDefect....

Since the subsequent query gets its data from there no other change is required.

Also if you want it to be the last 90 days it shoud be greater then or equal to 90 days.... >90

Update of your application attached.
SELECT DISTINCT KO_QN_Data.Field20, KO_QN_Data.[DefectQty (ext)]

FROM KO_QN_Data

WHERE (((KO_QN_Data.[Created on])>=Date()-90))

ORDER BY KO_QN_Data.[DefectQty (ext)] DESC , KO_QN_Data.Field20;

Open in new window

KO-Quality-Master-2008-Linked-20.mdb
0
 

Author Comment

by:Rex85
ID: 22180513
When I run Rick's query, I get the response shown below. What I am wanting is shown in the Desiredqryresults image, which I made up in Excel. Note: I would like to show ties as the same number as is highlighted at #7.

the other complication, which I mentioned above, is that I need to limit the data returned through a WHERE clause as attached below
WHERE GetWeekEndingDate([Created on])>=Date()-90 AND (KO_QN_Data.[Name 1]="KO-Borden" OR KO_QN_Data.[Name 1]="flexcel-Borden") AND GetBucket([Code group])="Product" AND Clng(TransformTextFields([KO_QN_Data.DefectQty (ext)]))>0

 

Open in new window

qryresults.jpg
Desiredqryresults.jpg
0
 

Author Closing Comment

by:Rex85
ID: 31470291
Thanks. Close Enough to do me good.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Suggested Solutions

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now