Hi,
Where the "row number in the answer set" does come in useful (or at least
where I've used it a number of itmes) is when populating a table with the
results of a select and including this value as another column in that
table.
"build a table with the top 10 selling items over the last week"
Insert into target-table
Select productid
,sum(sales)
,row-number-in-answer-set as sales_rank
>From sales_table...
This involves a single sql request with no returning of data rows back to
the application. Very easy to code and execute.
The above table can then be compared to an equivalent list of products from
the previous week - this then forms the basis of a lot of useful analysis.
Cheers,
Dave
Ward Analytics Ltd - information in motion
Tel: +44 (0) 118 9740191
Fax: +44 (0) 118 9740192
www: http://www.ward-analytics.com
Registered office address: The Oriel, Sydenham Road, Guildford, Surrey,
United Kingdom, GU1 3SR
Registered company number: 3917021 Registered in England and Wales.
-----Original Message-----
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Alex Bowden
Sent: 01 July 2013 12:07
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Is there a way to return the row number? (NOT the
rowid)
> This would not be something you would sort by.
And what if I do?
> It should be assigned a value only during final 'display' of the query
after all 'sorts' of operations are done with.
Oh great. So the user is supposed to understand the implementation, in
order to understand what the results will be.
This would be just another nail in the coffin of relationality and
simplicity, on a minor whim.
On 1 Jul 2013, at 11:01, Tony Papadimitriou <to...@acm.org> wrote:
>
> -----Original Message----- From: Alex Bowden
> Sent: Monday, July 01, 2013 12:46 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Is there a way to return the row number? (NOT
therowid)
>
>
> I can't wait to try
>
> order by row_number desc
>
>
> On 1 Jul 2013, at 10:33, Tony Papadimitriou <to...@acm.org> wrote:
>
>> Is there a function (or method), e.g., row(), to return the sequence
number of the selected row? This is not the same as ROWID. row() should
give a sequence number always starting from 1 up the to the number of rows
returned by the view/select etc.
>>
>> If not, then please add to the wish list.
>>
>> TIA
>> _______________________________________________
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
