Re: [sqlite] update to limits infomation

[Leslie S Satenstein]

Since your table is small, you should
clear out the rank column

Then do a select 


 
Regards  
 Leslie
 Mr. Leslie Satenstein
50 years in Information Technology and going strong.
Yesterday was a good day, today is a better day,
and tomorrow will be even better.
 
SENT FROM MY OPEN SOURCE FEDORA LINUX SYSTEM.

mailto:lsatenst...@yahoo.com
alternative: leslie.satenst...@itbms.biz 
www.itbms.biz  www.eclipseguard.com
 

--- On Mon, 2/4/13, YAN HONG YE <yanhong...@mpsa.com> wrote:

From: YAN HONG YE <yanhong...@mpsa.com>
Subject: [sqlite] update to limits infomation
To: "sqlite-users@sqlite.org" <sqlite-users@sqlite.org>
Date: Monday, February 4, 2013, 8:22 PM

I hava a table like this:
id,name,score,rank
1,anna,80,0
2,qera,65,0
6,kero,90,0
10,rosa,95,0

what I would like to do is to update the rank position.  I have this,

update mytable set rank= 1 where max(score);
update mytable set rank= 2 where max(score-1) ;


the result should be like this:
id,name,score,rank
1,anna,80,3
2,qera,65,4
6,kero,90,2
10,rosa,95,1

___
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

Re: [sqlite] update to limits infomation

[James K. Lowden]

On Tue, 5 Feb 2013 01:22:37 +
YAN HONG YE  wrote:

> I hava a table like this:
> id,name,score,rank
> 1,anna,80,0
> 2,qera,65,0
> 6,kero,90,0
> 10,rosa,95,0
> 
> what I would like to do is to update the rank position.  I have this,

The rank can be derived, obviating the need to update it:

create view vscores as
   select   1 + count(b.score) as rank
  , a.name
  , a.score
  , a.id 
   from scores as a 
   left join scores as b
   on a.score < b.score
   group by a.name
  , a.score
  , a.id 
   ;

sqlite3 scores.db 'select * from vscores order by score desc;'

ranknamescore   id
--  --  --  --
1   rosa95.010
2   kero90.06 
3   anna80.01 
4   qera65.02 

--jkl
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] update to limits infomation

[Yongil Jang]

Suggestion for new development item of sqlite?
I just mentioned this idea because of I thought that it is helpful for the
others if it can be implemented.

Your answer is correct.
But, n x n times of comparation would be occurred to count bigger number to
set rank of each record in mytable can make this query to be slow.
And, in my experience, there was some requirement of sequence number in my
work area.

Regards,
Yongil Jang.


2013/2/5 Igor Tandetnik 

> On 2/4/2013 9:37 PM, Yongil Jang wrote:
>
>> For example, if sqlite supports sequence() function that returns current
>> sequence number of result set then it can be used for this case, doesn't
>> it?
>>
>
> If SQLite supported such a function, then it could be used. But it
> doesn't, so it can't. I'm not sure what point you are trying to make here.
>
> --
> Igor Tandetnik
>
> __**_
> 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

Re: [sqlite] update to limits infomation

[Igor Tandetnik]

On 2/4/2013 9:37 PM, Yongil Jang wrote:

For example, if sqlite supports sequence() function that returns current
sequence number of result set then it can be used for this case, doesn't it?


If SQLite supported such a function, then it could be used. But it 
doesn't, so it can't. I'm not sure what point you are trying to make here.

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] update to limits infomation

[Yongil Jang]

I have a question.

For example, if sqlite supports sequence() function that returns current
sequence number of result set then it can be used for this case, doesn't it?

ex)
select sequence(), * from mytable order by score desc;
1, 10,rosa,95,0
2, 6,kero,90,0
3, 1,anna,80,0
4, 2,qera,65,0

This runtime values on result set may help to solve some query scenarios.


2013/2/5 Igor Tandetnik 

> On 2/4/2013 8:34 PM, Igor Tandetnik wrote:
>
>> On 2/4/2013 8:22 PM, YAN HONG YE wrote:
>>
>>> I hava a table like this:
>>> id,name,score,rank
>>> 1,anna,80,0
>>> 2,qera,65,0
>>> 6,kero,90,0
>>> 10,rosa,95,0
>>>
>>> what I would like to do is to update the rank position.
>>>
>>
>> update mytable set rank = (select count(*) from mytable t2 where
>> t2.score <= mytable.score);
>>
>
> Sorry, that should bet2.score >= mytable.score
>
>
> --
> Igor Tandetnik
>
> __**_
> 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

Re: [sqlite] update to limits infomation

[Igor Tandetnik]

On 2/4/2013 8:22 PM, YAN HONG YE wrote:

I hava a table like this:
id,name,score,rank
1,anna,80,0
2,qera,65,0
6,kero,90,0
10,rosa,95,0

what I would like to do is to update the rank position.


update mytable set rank = (select count(*) from mytable t2 where 
t2.score <= mytable.score);


--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] update to limits infomation

[YAN HONG YE]

I hava a table like this:
id,name,score,rank
1,anna,80,0
2,qera,65,0
6,kero,90,0
10,rosa,95,0

what I would like to do is to update the rank position.  I have this,

update mytable set rank= 1 where max(score);
update mytable set rank= 2 where max(score-1) ;


the result should be like this:
id,name,score,rank
1,anna,80,3
2,qera,65,4
6,kero,90,2
10,rosa,95,1

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users