How to get the row number in an SQL query?

Question

Question

Anderson Negreli · Mar 25, 2021

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.

Product version: Caché 2018.1

$ZV: Cache for Windows (x86-64) 2018.1.3 (Build 414U)

Discussion (7)3

Log in or sign up to continue

Timothy Leavitt · Mar 25, 2021

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)

1 0

Robert Cemper · Mar 25, 2021

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

0 0

Robert Cemper · Mar 27, 2021

a nice description of what a sub-select does preparing the resultset

0 0

score 1 · Answer 1 · 2021-03-25T10:08:22-04:00

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.

score 0 · Answer 2 · 2021-03-25T17:36:02-04:00

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.

score 0 · Answer 3 · 2021-03-25T12:04:55-04:00

Gabriel Vellasq... · Mar 25, 2021

Have you tried using group by?

0 0

score 0 · Answer 4 · 2021-03-25T13:40:20-04:00

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.