Rozamunda
asked on
SQL DB/2 for AS/400 question V.6.1 - OVER
Hi, i want to sort by calculated field and then use over to retrieve records within certain range, it works when i sorted by a field which is in the table, but not calculated:
SELECT * FROM ( SELECT ROW_NUMBER() OVER(ORDER BY fld1,fld2,DIFF
) as ROW, 3*fld1 as REV,fld2-3*fld1 as
DIFF
FROM FILE1
WHERE fld4 =1 ) as ALL
WHERE ALL.ROW BETWEEN 1 AND 1000
Last Comment
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
slinkygn
Easiest way may be to create your search as a view without ROW_NUMBER() OVER(), and then do another SELECT on the view to get the ROW_NUMBER in there. (Might also be able to do the same thing with a WITH clause, but I'm not so sure about that.)
Databases
Databases are organized collections of data, most commonly accessed through management systems including schemas, tables, queries and processes that allow users to enter and manipulate the information or utilize it in other fashions, such as with web applications or for reporting purposes.
62K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
