On 2015-08-20 07:44 AM, Anthrathodiyil, Sabeel (S.) wrote:
> Hi,
> I think there is ambiguity in the example I provided, I wouldn't need a
> ranking in my case as I need the offset (think row number is misleading) of
> the first record in the sorted list.
>
> Here is my use case.
> Find the ro
John McKown
Sent: Wednesday, August 19, 2015 6:32 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Getting row number in a sorted list.
On Wed, Aug 19, 2015 at 7:40 AM, Simon Slavin wrote:
>
> On 19 Aug 2015, at 1:16pm, Anthrathodiyil, Sabeel (S.) <
> santhrat a
>
> SELECT count(sub.Name) + 1 AS Rank, a.Name
> FROM NameTable AS a LEFT OUTER JOIN NameTable AS sub ON sub.Name <
> a.Name
> WHERE a.Name LIKE 'P%'
> GROUP BY a.Name
> ORDER BY a.Name
> LIMIT 1
>
>
> -- Rank | Name
> -- | --
> -- 4 | PQRS
I shou
On 2015-08-19 03:02 PM, John McKown wrote:
> On Wed, Aug 19, 2015 at 7:40 AM, Simon Slavin wrote:
>
>> 1|ABCD
>> 2|CDE
>> 4|AXN
>> 5|AXN2
>> 6|PQRS2
>> sqlite> select rowid from NameTable where Name between 'P' and 'PZZZ'
>> limit 1;
>> 6
>>
>>
>> Hum, that probably isn't what the OP wanted.
On 19 Aug 2015, at 2:00pm, R.Smith wrote:
> Seriously though, if that column is not COLLATE NOCASE declared, 'PZZZ' will
> fail. Either ensure your column has COLLATE NOCASE or perhaps simply choosing
> the highest (non UTF-8) character such as:
>
> WHERE name BETWEEN 'P' AND 'P~'
>
>
Simon Slavin wrote:
> SELECT rowid FROM NameTable
> WHERE name BETWEEN 'P' AND 'P'
>
> This will execute faster if you have an index on 'name' in NameTable.
>
> [Yes I know 'P' is lazy. Until you find someone with that name
> (presumably Polish) with that name bite me.]
If the colu
On 2015-08-19 02:40 PM, Simon Slavin wrote:
>
> SELECT rowid FROM NameTable
> WHERE name BETWEEN 'P' AND 'P'
> ORDER BY name
> LIMIT 1
>
> This will execute faster if you have an index on 'name' in NameTable.
>
> [Yes I know 'P' is lazy. Until you find someone with that
On 19 Aug 2015, at 1:16pm, Anthrathodiyil, Sabeel (S.) wrote:
> Example, for below entries in NameTable
>
> Name
>
> 1. PTN
>
> 2. ABCD
>
> 3. CDE
>
> 4. PQRS
>
> 5. AXN
>
>
> I want to get the row number of the first name that starts with 'P' in the
> sort
Hi,
I have a database with NameTable having name records in it, I need to get the
row number of the first record in the sorted list of names for which the search
name matches.
Example, for below entries in NameTable
Name
1. PTN
2. ABCD
3. CDE
4. PQRS
5. AXN
On Wed, Aug 19, 2015 at 7:40 AM, Simon Slavin wrote:
>
> On 19 Aug 2015, at 1:16pm, Anthrathodiyil, Sabeel (S.) <
> santhrat at visteon.com> wrote:
>
> > Example, for below entries in NameTable
> >
> > Name
> >
> > 1.
> ??
> PTN
> >
> > 2. ABCD
> >
> > 3. CDE
> >
> > 4. PQRS
>
10 matches
Mail list logo
