 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 1 / March 2006creating a row number within an Acess SQL query
I've seen this asked several times, but never a straight answer. I have a query returning a set of sorted rows. Let's say I get back: Alice Bob Charlie I want to add a number ranking, so it becomes: 1 | Alice 2 | Bob 3 | Charlie Is there some way to place a variable within the SQL statement that increments by 1 for each row? blue875@carolina.rr.com wrote in message <1143657653.678570.123220@j33g2000cwa.googlegroups.com> : > I've seen this asked several times, but never a straight answer. > I have a query returning a set of sorted rows. Let's say I get back: [quoted text clipped - 11 lines] > Is there some way to place a variable within the SQL statement that > increments by 1 for each row? Something like this? select (select count(s.name) from mytable s where s.name < = t.name) as mycount, t.name from mytable t order by t.name This will probably be a drain on recourses (if it works), if there are lot of rows, and the field to sort on/set criteria on, needs to be unique.  SignatureRoy-Vidar It's a small number of rows coming in the query, and uniqueness doesn't need to be enforced in this instance. Your query ALMOST worked. It did give a number, but not in the right order or at the right time. Explanation: If my original data set I queried was set up like: Charlie Alice Bob Then your query returned this: 2 | Alice 3 | Bob 1 | Charlie It used the original position, not the position after sorting. I can try passing my query results through another query that does nothing but add your number to it, but I'd like something a little cleaner, a "one shot" kind of a deal. blue875@carolina.rr.com wrote in message <1143658929.509886.55250@g10g2000cwb.googlegroups.com> : > It's a small number of rows coming in the query, and uniqueness doesn't > need to be enforced in this instance. [quoted text clipped - 15 lines] > nothing but add your number to it, but I'd like something a little > cleaner, a "one shot" kind of a deal. Strange ... When I stuffed a table only containing one field (name) with Alice, Bob, and Charlie, then run the excact query I gave you, it resulted in exactly what you asked for in the initial post. As you can see by the SQL, for this to work, the field in question will need to be 1 - unique 2 - be the field on which you sort 3 - be the field used in the comparision in the subquery For more info, try searching on "ranking query", here are some links (watch for linebreaks) http://support.microsoft.com/?kbid=208946 http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsmart01/html/ sa01j1.asp SignatureRoy-Vidar Probably because without any SORT BY clause in the query, Access will natively sort by the table's primary key, which in the OP's post I'd guess is an autonumber field. Just a guess. Much warmth, planetthoughtful --- "Lost in thought" http://www.planetthoughtful.org planetthoughtful wrote in message <1143683644.917026.67890@t31g2000cwb.googlegroups.com> : > Probably because without any SORT BY clause in the query, Access will > natively sort by the table's primary key, which in the OP's post I'd > guess is an autonumber field. As I've understood, an ORDER BY clause, is not necessary for an ordinary ranking query to work, I like to add it though, to present the result in the same order as the ranking, but again, I don't think it is entirely necessary. If you look again, you should be able to find an ORDER BY clause as the last clause of my suggested SQL statement. Using Count(*), will usually also do, in stead of Count(FieldName), I don't know why I keep using the latter version. But I do think you need a sortable and unique *field*, on which to set the criterion in the subquery. If the field isn't unique, equal values/duplicates will get equal rank. For more samples/explanations, check out for instance http://support.microsoft.com/kb/q182568/ http://www.fabalou.com/Access/Queries/RunningTotalQuery.asp http://ourworld.compuserve.com/homepages/attac-cg/AqryTip.htm#AUTONUM If I've competely misunderstood the concept of "Ranking queries" or "Running totals", I'm happy to learn new ways (except usage of Domain Aggregates, that is). SignatureRoy-Vidar > I've seen this asked several times, but never a straight answer. > I have a query returning a set of sorted rows. Let's say I get back: [quoted text clipped - 11 lines] > Is there some way to place a variable within the SQL statement that > increments by 1 for each row? Try this thread: http://groups.google.com/group/comp.databases.ms-access/browse_frm/thread/db227b e5c3a2d2bb/cdb73e9bfb888698#cdb73e9bfb888698 James A. Fortune CDMAPoster@FortuneJames.com The normal way is to add an AutoNumber field to your table. Then Access will create a sequential number for you. But if you you run a query on that field, you might get 12 Bob 13 Charlie 14 Alice So the auto-number depends on the order in which you created the records. > I've seen this asked several times, but never a straight answer. > I have a query returning a set of sorted rows. Let's say I get back: [quoted text clipped - 11 lines] > Is there some way to place a variable within the SQL statement that > increments by 1 for each row? SignatureUsing Opera's revolutionary e-mail client: http://www.opera.com/mail/ All things are possbile. This is. Some things are unnecessary. This is. Some things are foolish. This is. > All things are possbile. This is. > Some things are unnecessary. This is. > Some things are foolish. This is. This reminds me of the time an itinerant street preacher came to Oakland U. That preacher called Becky a whore. That wasn't nice. Of course, everybody on campus knows he was right; but it still wasn't nice. -- Ken Harrington Putting a row number on an Access query does fly 180 degrees opposite that flown by database theory. Maybe this will be the simplest way to solve some future problem. Maybe not. The future's not ours to see :-). James A. Fortune CDMAPoster@FortuneJames.com |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.