How to get the row number in an SQL query?
Hi,
I'm doing a query in SQL and I need to sort my data by some non-repeated field.
Unfortunately, my data is grouped in a way that I cannot guarantee that any column will not have repeated data, so one solution would be to take the row number.
Also, the Cache is not accepting Row_Number () in my querry and I would like to know if there is another solution to return line numbers or some way to add this function to the Cache.
Best regards.
Comments
PS: I could use the ID to get unique values, however I can only sort displayed values and I don't want to show a meaningless number (as it would be if I used the ID), so the row number would be ideal.
You can run a query like SELECT * FROM someTable ORDER BY %ID and it will order by the row ID without including it as part of the result set. Ultimately we would need more information (like the query you're running) to provide additional answers, but ORDER BY %ID sounds like what you want to use.
The closest thing to what you're looking for is %VID (link to documentation)
You can use this even without an actual view as follows (for example):
select %VID "RowNumber",* from (select top all AirportLocation, FAADistrictOffice from Aviation.Event order by FAADistrictOffice, AirportLocation)
Have you tried using group by?
similar to the proposal of @Timothy Leavitt you use a Sub-Select for sorting
and do the output in the outer SELECT
SELECT Name, SSN, Home_State, Home_Street, Age, Company, DOB FROM (
SELECT top all * FROM Sample.Person
order by ID desc
)
As you use Caché you may try in Namespace SAMPLES
Not sure if any of the replies are resolving your issue yet but a long winded but definite way of achieving your goal would be to load the data values into a incrementing global i.e. set ^SQLWK($i(SQLWKvariable))=SQL values. You can then run down the global instead of your query result set.
a nice description of what a sub-select does preparing the resultset