To add a counter specifically for one record using SQL/SAS, you can use a combination of sub-queries and window functions.
Example:
SELECT COUNT(*) FROM mytable WHERE id < desired_id;
Example:
SELECT desired_id, ROW_NUMBER() OVER (ORDER BY desired_id) + (SELECT COUNT(*) FROM mytable WHERE id < desired_id) as counter
FROM mytable
WHERE id = desired_id;
This query selects the desired record and adds a counter column that starts from 1 and increments by one for each subsequent record. The ORDER BY
clause determines the order of the counter, in this case, it's ordered by the desired_id
field. The sub-query calculates the count of records before the desired one and adds it to the counter value.
Please start posting anonymously - your entry will be published after you log in or create a new account. This space is reserved only for answers. If you would like to engage in a discussion, please instead post a comment under the question or an answer that you would like to discuss
Asked: 2023-06-23 10:23:45 +0000
Seen: 14 times
Last updated: Jun 23 '23
How can ADOMD.NET be used to deploy UDFs (User defined functions) to SSAS?
What is the SAS coding method to change Row 1 into a variable without using proc SQL?
What is the best way to arrange the file structure for both the backend and frontend in MERN?
Is it possible to utilize a SAS Server for storing network files? If so, what is the process?