On 2017/01/26 6:23 PM, dspub...@freemail.hu wrote:
Thanks for responses, I have an other problem
select * from (select row_number(name) as id,name from example order by
name desc) t order by name In this query, the server why doesn't use the
nested "order by"?
I want to numbering the inner data descending, but I can't, because looks
like, the server ignores it.
There exists no such thing as "nested" ORDER BY clauses. One and only one
ORDER BY clause can ever determine ordering. The outer-most is the Boss.
What you perhaps are trying to achieve is this:
SELECT COUNT(B.name) AS id, A.name
FROM example AS A
LEFT JOIN example AS B ON B.name < A.name
GROUP BY A.name
ORDER BY A.name
Couldn't test it so there might be a typo in there, but the principle
works.
Actually , I want to adopt some sql analytic function with application
defined function, and I want to use them with "with" statement.
For example:
with
seg1 (account_num,account_type) as (select account_num,account_type from
account where account_name like 'p%' )
,seg2 (t10,t10rid,t12,t12rid) as (select t10.account_num
,t10.rid,t12.item,t12.rid
from (select *,row_number() as rid from seg1 order by account_num) t10
left join (select *,row_number() as rid from account_sub order by item DESC)
t12 on t12.account_num=t10.account_num
)
select * from seg2 where t10rid<=5 order by t10rid,t12rid
('00032', 1, 'a', 1)
('00032ST', 2, 'a', 1)
('0014615', 3, 'a', 1)
('001604313', 4, 'A', 1) problem is here, it should be ('001604313', 4, 1,
'B') because of order by item DESC
('001604313', 4, 'B', 2)problem is here, it should be ('001604313', 4, 1,
'A') because of order by item DESC
('001610179', 5, 'a', 1)
Thanks,
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
