Re: [sqlite] sequential row numbers from query

[Simon Slavin]

On 3 May 2013, at 2:22am, James K. Lowden  wrote:

> I considered writing such a function, too.  I didn't because relations
> have no order.  
> 
> One might be tempted to say that's solved by ORDER BY.  But 
> 
> 1.  It's not clear that the rows are presented to the UDF in ORDER BY
> order.  There's no reason they should be, and a few they shouldn't,
> including
> 
> 2.  Subqueries can't have ORDER BY, making the UDF non-deterministic by
> definition.  

Also, ORDER BY can be on a non-unique field.  So even with an ORDER BY you can 
sometimes get two different orders.  Theoretically.

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

Re: [sqlite] sequential row numbers from query

[Yongil Jang]

Hi, all.

How about this approach?
I just implemented a simple code(User Defined Function) that returns a
number as like as row number.

It was worked pretty good with my simple SQL test cases.

sqlite> insert into test values ('first record');
sqlite> insert into test values ('second record');
sqlite> insert into test values ('third record');
sqlite> select rownum(0), * from test;
1 | first record
2 | second record
3 | third record

Note. parameter value of 0 is not necessary, but it should be exist to work
properly to use aux data in UDF.

Below shows my code.
It is registered by calling sqlite3_create_function() after database is
opened.

typedef struct ROWNUM_t ROWNUM_t;
struct ROWNUM_t{
  int nNumber;
};

static void rownum_free(void *p){
  sqlite3_free(p);
}

static void rownum(
  sqlite3_context *context,
  int argc,
  sqlite3_value **argv
){
  ROWNUM_t* pAux;

  pAux = sqlite3_get_auxdata(context, 0);

  if(!pAux) {
pAux = (ROWNUM_t*)sqlite3_malloc(sizeof(ROWNUM_t));
if(pAux) {
  pAux->nNumber = 0;
  sqlite3_set_auxdata(context, 0, (void*)pAux, rownum_free);
}
else {
  sqlite3_result_error(context, "sqlite3_malloc failed", -1);
  return;
}
  }
  pAux->nNumber++;

  sqlite3_result_int(context, pAux->nNumber);
}

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

Re: [sqlite] sequential row numbers from query

[Bart Smissaert]

Hi Hitesh,

Here all the VB6 code to do with this. Ignore all the Debug stuff and also
all the RaiseEvent lines. Note that this uses the free VB SQLite wrapper
from Olaf Schmidt and if you don't use that then that is very much
recommended. Let me know if you want that and I will explain.

Also note that my code does something slightly different then what you want
to do, but still, it might be useful.

Bart


Public Function SetSequentialGroups(strDB As String, _
strTable As String, _
strGroupField As String, _
strIDField As String, _
strCompareField1 As String, _
Optional strCompareField2 As String, _
Optional strCompareField3 As String, _
Optional lFirstGroupNumber As Long, _
Optional bLog As Boolean, _
Optional bDebug As Boolean) As Long

Dim i As Long
Dim c As Long
Dim cRs As cRecordset
Dim cCmd As cCommand
Dim lGroupIdx As Long
Dim bDoGroupSwitch As Boolean
Dim lCompareFields As Long
Dim V1
Dim V2
Dim V3  'compare-values as variant
Dim lFieldCount As Long
Dim lCompareFieldNumber1 As Long  'all these 4 0-based for
convenience
Dim lCompareFieldNumber2 As Long
Dim lCompareFieldNumber3 As Long
Dim lIDFieldNumber As Long

10  On Error GoTo ERROROUT

20  SetSQLiteConn strDB, , , False

30  If SQLiteTableExists(strTable, strDB, False, True) = False Then
40SetSequentialGroups = -1
50Exit Function
60  End If

70  If FieldNumberInTable(strDB, strTable, strGroupField, , False) < 1
Then
80SetSequentialGroups = -1
90Exit Function
100 End If

110 If FieldNumberInTable(strDB, strTable, strIDField, , False) < 1 Then
120   SetSequentialGroups = -1
130   Exit Function
140 End If

150 If FieldNumberInTable(strDB, strTable, strCompareField1, , False) <
1 Then
160   SetSequentialGroups = -1
170   Exit Function
180 End If

190 lGroupIdx = lFirstGroupNumber  'initilize the first lGroupIdx

200 Set cRs = Cnn.OpenRecordset("SELECT * FROM " & strTable & _
" ORDER BY " & strIDField & " ASC")

210 lFieldCount = cRs.Fields.Count

220 If Len(strCompareField2) = 0 Then
230   lCompareFields = 1
240 Else
250   If Len(strCompareField3) > 0 Then
260 lCompareFields = 3
270   Else
280 lCompareFields = 2
290   End If
300 End If

'IndexInFieldList is zero based
'--
310 lIDFieldNumber = cRs.Fields(strIDField).IndexInFieldList
320 lCompareFieldNumber1 = cRs.Fields(strCompareField1).IndexInFieldList

330 If lCompareFields > 1 Then
340   lCompareFieldNumber2 =
cRs.Fields(strCompareField2).IndexInFieldList
350 End If

360 If lCompareFields > 2 Then
370   lCompareFieldNumber3 =
cRs.Fields(strCompareField3).IndexInFieldList
380 End If

390 If bDebug Then
400   MsgBoxDLL "lFieldCount" & vbTab & lFieldCount & vbCrLf & _
"lIDFieldNumber" & vbTab & lIDFieldNumber & vbCrLf & _
"lCompareFieldNumber1" & vbTab & lCompareFieldNumber1 &
vbCrLf & _
"lCompareFieldNumber2" & vbTab & lCompareFieldNumber2 &
vbCrLf & _
"lCompareFieldNumber3" & vbTab & lCompareFieldNumber3 &
vbCrLf & _
"lCompareFields" & vbTab & lCompareFields, _
"Parameters of SetSequentialGroups", _
lFormColour:=lColourForm, bLineUpTabs:=True
410 End If

420 Set cCmd = Cnn.CreateCommand("UPDATE " & strTable & _
 " SET " & strGroupField & " = ? WHERE
" & _
 strIDField & " = ?")

430 If bLog Then
440   ShowStatement "Procedure SetSequentialGroups", , , 2, True, ,
strDB
450 End If

460 BeginTransaction strDB, False

470 Select Case lCompareFields

  Case 1

'now we work with valuematrix for more speed
480 V1 = cRs.ValueMatrix(0, lCompareFieldNumber1)

490 For i = 0 To cRs.RecordCount - 1
  'we split up the comparisons, for a little bit more speed (VB
has no "early exit" in combined If-conditions)
500   If cRs.ValueMatrix(i, lCompareFieldNumber1) <> V1 Then
510 bDoGroupSwitch = True
520   Else
530 bDoGroupSwitch = False
540   End If

550   If bDoGroupSwitch Then  'set the next set of compare-values
560 V1 = cRs.ValueMatrix(i, lCompareFieldNumber1)
570 lGroupIdx = lGroupIdx + 1
580   End If

590   cCmd.SetInt32 1, lGroupIdx
600  

Re: [sqlite] sequential row numbers from query

[Bart Smissaert]

Hi Hitesh,

Attached all the VB6 code to do with this. Ignore all the Debug stuff and
also all the RaiseEvent lines. Note that this uses the free VB SQLite
wrapper from Olaf Schmidt and if you don't use that then that is very much
recommended. Let me know if you want that and I will explain.

Also note that my code does something slightly different then what you want
to do, but still, it might be useful.

Bart


On Sat, Apr 27, 2013 at 2:12 PM, hiteshambaliya
wrote:

> Ya I am interested to know more.
>
> My mail ID hitesh.ambal...@gmail.com
>
> Thank you so much
>
>
>
> --
> View this message in context:
> http://sqlite.1065341.n5.nabble.com/sequential-row-numbers-from-query-tp47370p68515.html
> Sent from the SQLite mailing list archive at Nabble.com.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
Public Function SetSequentialGroups(strDB As String, _
strTable As String, _
strGroupField As String, _
strIDField As String, _
strCompareField1 As String, _
Optional strCompareField2 As String, _
Optional strCompareField3 As String, _
Optional lFirstGroupNumber As Long, _
Optional bLog As Boolean, _
Optional bDebug As Boolean) As Long

Dim i As Long
Dim c As Long
Dim cRs As cRecordset
Dim cCmd As cCommand
Dim lGroupIdx As Long
Dim bDoGroupSwitch As Boolean
Dim lCompareFields As Long
Dim V1
Dim V2
Dim V3  'compare-values as variant
Dim lFieldCount As Long
Dim lCompareFieldNumber1 As Long  'all these 4 0-based for convenience
Dim lCompareFieldNumber2 As Long
Dim lCompareFieldNumber3 As Long
Dim lIDFieldNumber As Long

10  On Error GoTo ERROROUT

20  SetSQLiteConn strDB, , , False

30  If SQLiteTableExists(strTable, strDB, False, True) = False Then
40SetSequentialGroups = -1
50Exit Function
60  End If

70  If FieldNumberInTable(strDB, strTable, strGroupField, , False) < 1 Then
80SetSequentialGroups = -1
90Exit Function
100 End If

110 If FieldNumberInTable(strDB, strTable, strIDField, , False) < 1 Then
120   SetSequentialGroups = -1
130   Exit Function
140 End If

150 If FieldNumberInTable(strDB, strTable, strCompareField1, , False) < 1 
Then
160   SetSequentialGroups = -1
170   Exit Function
180 End If

190 lGroupIdx = lFirstGroupNumber  'initilize the first lGroupIdx

200 Set cRs = Cnn.OpenRecordset("SELECT * FROM " & strTable & _
" ORDER BY " & strIDField & " ASC")

210 lFieldCount = cRs.Fields.Count

220 If Len(strCompareField2) = 0 Then
230   lCompareFields = 1
240 Else
250   If Len(strCompareField3) > 0 Then
260 lCompareFields = 3
270   Else
280 lCompareFields = 2
290   End If
300 End If

'IndexInFieldList is zero based
'--
310 lIDFieldNumber = cRs.Fields(strIDField).IndexInFieldList
320 lCompareFieldNumber1 = cRs.Fields(strCompareField1).IndexInFieldList

330 If lCompareFields > 1 Then
340   lCompareFieldNumber2 = cRs.Fields(strCompareField2).IndexInFieldList
350 End If

360 If lCompareFields > 2 Then
370   lCompareFieldNumber3 = cRs.Fields(strCompareField3).IndexInFieldList
380 End If

390 If bDebug Then
400   MsgBoxDLL "lFieldCount" & vbTab & lFieldCount & vbCrLf & _
"lIDFieldNumber" & vbTab & lIDFieldNumber & vbCrLf & _
"lCompareFieldNumber1" & vbTab & lCompareFieldNumber1 & 
vbCrLf & _
"lCompareFieldNumber2" & vbTab & lCompareFieldNumber2 & 
vbCrLf & _
"lCompareFieldNumber3" & vbTab & lCompareFieldNumber3 & 
vbCrLf & _
"lCompareFields" & vbTab & lCompareFields, _
"Parameters of SetSequentialGroups", _
lFormColour:=lColourForm, bLineUpTabs:=True
410 End If

420 Set cCmd = Cnn.CreateCommand("UPDATE " & strTable & _
 " SET " & strGroupField & " = ? WHERE " & _
 strIDField & " = ?")

430 If bLog Then
440   ShowStatement "Procedure SetSequentialGroups", , , 2, True, , strDB
450 End If

460 BeginTransaction strDB, False

470 Select Case lCompareFields

  Case 1

'now we work with valuematrix for more speed
480 V1 = cRs.ValueMatrix(0, lCompareFieldNumber1)

490 For i = 0 To cRs.RecordCount - 1
 

Re: [sqlite] sequential row numbers from query

[hiteshambaliya]

Ya I am interested to know more.

My mail ID hitesh.ambal...@gmail.com

Thank you so much



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/sequential-row-numbers-from-query-tp47370p68515.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sequential row numbers from query

[Bart Smissaert]

Have a look at this thread in the archive:
find sequential groups
It can be done with SQL, but it is slow and it can be done enormously
faster in code.
I did this in VB6 and let me know if you are interested and I mail you the
code off-list.

RBS



On Sat, Apr 27, 2013 at 8:10 AM, hiteshambaliya
wrote:

> You are absolutely right but,
> I am using VB 6.0 and i have global general function which fill the data in
> grid so there is if serial number column in query is easy way so..
>
> Any way to do in query???
>
>
>
> --
> View this message in context:
> http://sqlite.1065341.n5.nabble.com/sequential-row-numbers-from-query-tp47370p68506.html
> Sent from the SQLite mailing list archive at Nabble.com.
> ___
> 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] sequential row numbers from query

[hiteshambaliya]

You are absolutely right but,
I am using VB 6.0 and i have global general function which fill the data in
grid so there is if serial number column in query is easy way so..

Any way to do in query???



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/sequential-row-numbers-from-query-tp47370p68506.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sequential row numbers from query

[Igor Tandetnik]

On 4/26/2013 8:56 AM, hiteshambaliya wrote:

I want to get serial numbers for selected record from 1 to number of rows.
Your solution is right but in the situation when i want to sort by 'Party
Name' column then the serial number depend on autoid field arrange also as
sorted party name so i can't get it as serial records nos.

Please help me that what to do


While you iterate over the rows with sqlite3_step (or the equivalent in 
your language of choice), simply keep incrementing a counter.

--
Igor Tandetnik

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

Re: [sqlite] sequential row numbers from query

[hiteshambaliya]

Hi,
I want to get serial numbers for selected record from 1 to number of rows.
Your solution is right but in the situation when i want to sort by 'Party
Name' column then the serial number depend on autoid field arrange also as
sorted party name so i can't get it as serial records nos.

Please help me that what to do

 
Robert Citek-2 wrote
> How can I get a query to display sequential row number in a select
> statement?
> 
> I have a simple database similar to this:
> 
> $ sqlite3 db .dump
> BEGIN TRANSACTION;
> CREATE TABLE foo (field);
> INSERT INTO "foo" VALUES('a');
> INSERT INTO "foo" VALUES('b');
> INSERT INTO "foo" VALUES('c');
> COMMIT;
> 
> $ sqlite3 db 'select * from foo ; '
> a
> b
> c
> 
> I would like to do the equivalent of this in SQL:
> 
> $ sqlite3 db 'select * from foo order by field desc ; ' | cat -n
>  1  c
>  2  b
>  3  a
> 
> I have looked into rowid but it keeps the actual row id from the table
> and does not reorder the rowid based on the sort order.
> 
> Pointers to references appreciated.
> 
> Regards,
> - Robert
> ___
> sqlite-users mailing list

> sqlite-users@

> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users





--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/sequential-row-numbers-from-query-tp47370p68477.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sequential row numbers from query

[Adler, Eliedaat]

Possible limitations of temporary tables vs views:

1) Temporary tables are static - views are dynamic -
i.e. anytime a change is made to the database the temporary tables might need 
to be recreated.

2) Memory: Temporary tables will be retained in memory until dropped. Views 
will only use memory when used.

Eli Adler


-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Robert Citek
Sent: Saturday, March 28, 2009 12:03 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] sequential row numbers from query

That would work.  In fact, my current solution, which actually pipes to perl, 
works pretty well.  It's just that I have to then import the data back into the 
database.  So, I'd prefer to do the process entirely in SQL.

I was thinking maybe a view, but that didn't work.  Apparently, there is no 
rowid with views (or is there something equivalent that I'm overlooking?).

$ sqlite3 db 'create view bar as select * from foo order by field desc;
select rowid, * from bar ; '
|c
|b
|a

Substituting a temporary table for the view works:

$ sqlite3 db 'create temporary table bat as select * from foo order by field 
desc;
select rowid, * from bat ; '
1|c
2|b
3|a

Any limitations to consider when using a temporary table?  Any other ideas?

Regards,
- Robert

On Fri, Mar 27, 2009 at 3:37 PM, Thomas Briggs <t...@briggs.cx> wrote:
>   Holy cow that feels inefficient.
>
>   It's a bit clunky, but why not insert into a temporary table,
> ordered as desired, and then use the rowid from the temp table?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

This e-mail is confidential, the property of NDS Ltd and intended for the 
addressee only. Any dissemination, copying or distribution of this message or 
any attachments by anyone other than the intended recipient is strictly 
prohibited. If you have received this message in error, please immediately 
notify the postmas...@nds.com and destroy the original message. Messages sent 
to and from NDS may be monitored. NDS cannot guarantee any message delivery 
method is secure or error-free. Information could be intercepted, corrupted, 
lost, destroyed, arrive late or incomplete, or contain viruses. We do not 
accept responsibility for any errors or omissions in this message and/or 
attachment that arise as a result of transmission. You should carry out your 
own virus checks before opening any attachment. Any views or opinions presented 
are solely those of the author and do not necessarily represent those of NDS.

To protect the environment please do not print this e-mail unless necessary.

NDS Limited Registered Office: One London Road, Staines,Middlesex TW18 4EX, 
United Kingdom. A company registered in England and Wales Registered no. 
3080780 VAT no. GB 603 8808 40-00
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sequential row numbers from query

[Robert Citek]

That would work.  In fact, my current solution, which actually pipes
to perl, works pretty well.  It's just that I have to then import the
data back into the database.  So, I'd prefer to do the process
entirely in SQL.

I was thinking maybe a view, but that didn't work.  Apparently, there
is no rowid with views (or is there something equivalent that I'm
overlooking?).

$ sqlite3 db 'create view bar as select * from foo order by field desc;
select rowid, * from bar ; '
|c
|b
|a

Substituting a temporary table for the view works:

$ sqlite3 db 'create temporary table bat as select * from foo order by
field desc;
select rowid, * from bat ; '
1|c
2|b
3|a

Any limitations to consider when using a temporary table?  Any other ideas?

Regards,
- Robert

On Fri, Mar 27, 2009 at 3:37 PM, Thomas Briggs  wrote:
>   Holy cow that feels inefficient.
>
>   It's a bit clunky, but why not insert into a temporary table,
> ordered as desired, and then use the rowid from the temp table?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sequential row numbers from query

[Kees Nuyt]

On Fri, 27 Mar 2009 16:37:37 -0400, Thomas Briggs
 wrote:

>
>   Holy cow that feels inefficient.

Yes, it certainly is.

>   It's a bit clunky, but why not insert into a temporary table,
>ordered as desired, and then use the rowid from the temp table?

Yes, or solve it in the host language, which is what I would
do. Actually, OP's original:

sqlite3 db 'select * from foo order by field desc; '|cat -n

is pretty good.

Oh, well, there are 11 roads to Rome :)
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users