How can I put a running number in a View based on a Special Field: *special_field*,*name*,*surname*?
By submitting your email address, you agree to receive emails regarding relevant topic offers from TechTarget and its partners. You can withdraw your consent at any time. Contact TechTarget at 275 Grove Street, Newton, MA.
If I understand the question correctly, you want a "running number" that basically numbers the rows in a result set, presumably sorted.
So your results might be --
Special Running Field Name Surname Number AIF John Doe 1 BWH Bill Schmoe 2 CKR Mary Snow 3 KRU Fred Doh 4
This "running number" is also called a rank, and it is not easy to produce with SQL.
select Special_Field, Name, Surname , (select count(*) from yourTable where Special_Field <= OuterTable.Special_Field) as Running_Number from yourTable as OuterTable
For each row of the table in the outer query, the inner query counts the number of rows which have a special field that is less than or equal to the special field of the row of the outer query under consideration. So if the outer query is looking at the lowest row, AIF, then there will be only one row with a special field equal to or less than AIF -- namely, the same row. Hence you need "equal to or less than" and not just "less than."
Caution: this query may not work as expected if there are "ties" in the rankings; I'm sorry, I haven't tested it recently, and I cannot say with certainty that ties are handled properly. Also, not all database systems support the embedded subquery in the SELECT list.
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.