Question
Anderson Negreli · Mar 25, 2021

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.

#SQL #Caché
Product version: Caché 2018.1
$ZV: Cache for Windows (x86-64) 2018.1.3 (Build 414U)
30
3 0 7 117
Log in or sign up to continue

Replies

Anderson Negreli · Mar 25, 2021

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.

00
Kevin Chan · Mar 25, 2021

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.

00
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)
00
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


 

00
Steve Riddle · Mar 25, 2021

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.

00
Robert Cemper · Mar 27, 2021

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

00