Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 410
  • Last Modified:

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

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
Rex85
Asked:
Rex85
  • 17
  • 10
  • 8
  • +2
1 Solution
 
Daniel WilsonCommented:
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
 
Rex85Author Commented:
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
 
Daniel WilsonCommented:
OK, what is your table name?  And are those the only columns from it that you need?
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
Rex85Author Commented:
the table name would be FifteenthStWoodFPYParetoTable and yes, Defect and Qty are the only two columns in this table. Thanks very much.

Rex
0
 
mriz81Commented:
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
 
GRayLCommented:
You should qualify all the fields including * in the Select clauses with the table aliases.
0
 
mriz81Commented:
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
 
GRayLCommented:
Don't forget Select Count(t1.Defect)  in the SubQuery
0
 
Rex85Author Commented:
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
 
Daniel WilsonCommented:
They are 2 different aliases to the same table.

That is needed w/ any self-join or subselect on the same table.
0
 
Rex85Author Commented:
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
 
GRayLCommented:
That should be (SELECT Count(t1.Defect) FROM...
---------------------------------------^--this alias was omitted.
0
 
Rex85Author Commented:
( 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
 
GRayLCommented:
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
 
mriz81Commented:
Please use this subquery
( SELECT Count(t1.Field20) FROM  KO_QN_Data t1 WHERE  t1.QTY >= t2.QTY )

Open in new window

0
 
GRayLCommented:
You cannot have t2 as an alias for a different table name.  What does the total query now look like?
0
 
Rex85Author Commented:
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
 
Rex85Author Commented:
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
 
GRayLCommented:
Then the new table is KO_QN_Data, and the two fields are Field20 (Defect) and ?? (Qty)
0
 
Rex85Author Commented:
Yes. That is correct.
0
 
GRayLCommented:
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
 
Rex85Author Commented:
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
 
mriz81Commented:
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
 
mriz81Commented:
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
 
mriz81Commented:
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
 
Rex85Author Commented:
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
 
GRayLCommented:
Rex did you try my last post?
0
 
Rex85Author Commented:
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
 
mriz81Commented:
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
 
Rex85Author Commented:
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
 
GRayLCommented:
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
 
GRayLCommented:
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
 
mriz81Commented:
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
 
Rex85Author Commented:
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
 
Rick_RickardsCommented:
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
 
Rick_RickardsCommented:
Hey Rex85,

Did This work for you?

Rick
0
 
Rex85Author Commented:
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
 
Rex85Author Commented:
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
 
Rick_RickardsCommented:
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
 
Rex85Author Commented:
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
 
Rex85Author Commented:
Thanks. Close Enough to do me good.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

  • 17
  • 10
  • 8
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now