Query autonumber 1

[scottian]

How do i mimic an incremental number within a query?
i need a field within the query to show an incremental number.
i have created a select but i need one field of the query to show an autonumber?

Be ALERT - Your country needs Lerts

 

[lupins46]

You do this by counting the number of records with an id value less than the current id value.
In turn this means that there must be a field which is unique across all records in the query and the query must be sorted using this field.

Do you have such a field?

 

[r937]

while i personally love theta joins (join each row to every other row less than it), they can be slow

another option is to INSERT SELECT with an ORDER BY into a temp table that has an autonumber, then select from the temp table

rudy

http://r937.com/

SQL Consulting

 

[cochise]

I have a simple query that returns supplier and spend data; however, it returns thousands of records. I want to sort descending on spend value, and return only the top 50 records. How can I create a field that ranks by top spend?
I appreciate the help.

Here is my current SQL:

SELECT [Raw OPW SMD Dump].[Supplier Name], Sum([Raw OPW SMD Dump].[OPW Amount]) AS [OPW Total], [Raw OPW SMD Dump].Period
FROM [Raw OPW SMD Dump]
GROUP BY [Raw OPW SMD Dump].[Supplier Name], [Raw OPW SMD Dump].Period
HAVING ((([Raw OPW SMD Dump].Period)="2003"))
ORDER BY Sum([Raw OPW SMD Dump].[OPW Amount]) DESC;

 

[lespaul]

The query you already have should show you the records in order of amount spent (in your ORDER BY clause). To get only the top fifty add the TOP 50 to your query: (I also changed your having clause to a where clause)

SELECT TOP 50 [Raw OPW SMD Dump].[Supplier Name], Sum([Raw OPW SMD Dump].[OPW Amount]) AS [OPW Total], [Raw OPW SMD Dump].Period
FROM [Raw OPW SMD Dump]
WHERE ((([Raw OPW SMD Dump].Period)="2003"))
GROUP BY [Raw OPW SMD Dump].[Supplier Name], [Raw OPW SMD Dump].Period
ORDER BY Sum([Raw OPW SMD Dump].[OPW Amount]) DESC;

Leslie