Re: [sqlite] Incremental row number associated to the current Query?

[Kees Nuyt]

On Mon, 9 Aug 2010 17:53:02 -0500, Mike Henshaw
 wrote:

>>> Is there a way to create an incremental row or show a row number that is 
>>> linked to the current select query that can be used in
>>> calculations? 
>> 
>>> Basically a row counter for the current query that can be used in 
>>> calculations.
>
>>Your application makes a series of sqlite3_step calls, one for each row. 
>>Can't it just increment a counter on each step?
>
>>> 2. The application is written in C/CPP so no counters or variables from 
>>> C/CPP can be used.
>
>>I don't see how the second statement follows from the first. In fact, it 
>>appears that just the opposite should be the case.
>
>1. The application is from a third party with no access to the source code but 
>the SQL query can be updated since the SQL query is used in a custom HTML 
>template which can also be updated.
>2. The calculations to the RowNum would then have to be in the C/CPP 
>application which would nullify the use of customizable HTML template.
>
css will do that for you.

= Demo file 

http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd;>

http://www.w3.org/1999/xhtml; xml:lang="en"
lang="en">

Test pages


table { counter-reset: line-number; }
td:first-child:before {
content: counter(line-number) ".";
counter-increment: line-number;
padding-right: 0.3em; }




#. col1col2
val1.1val1.2
val2.1val2.1
val3.1val3.2



=== EOF ===
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Incremental row number associated to the current Query?

[Igor Tandetnik]

Mike Henshaw  wrote:
> 1. The application is from a third party with no access to the source code 
> but the SQL query can be updated since the SQL query
> is used in a custom HTML template which can also be updated. 
> 2. The calculations to the RowNum would then have to be in the C/CPP 
> application which would nullify the use of customizable HTML
> template. 

What kind of template language is being used? Many of them have a built-in way 
to number rows, or implement alternate coloring (the motivation for your second 
example, I suspect).

There is no built-in mechanism in SQLite for this kind of row numbering. 
Implementing it in SQL query is very inefficient, and is outright impossible if 
the resultset needs to contain duplicate rows.
-- 
Igor Tandetnik


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

Re: [sqlite] Incremental row number associated to the current Query?

[Mike Henshaw]

>> Is there a way to create an incremental row or show a row number that is 
>> linked to the current select query that can be used in
>> calculations? 
> 
>> Basically a row counter for the current query that can be used in 
>> calculations.

>Your application makes a series of sqlite3_step calls, one for each row. Can't 
>it just increment a counter on each step?

>> 2. The application is written in C/CPP so no counters or variables from 
>> C/CPP can be used.

>I don't see how the second statement follows from the first. In fact, it 
>appears that just the opposite should be the case.

1. The application is from a third party with no access to the source code but 
the SQL query can be updated since the SQL query is used in a custom HTML 
template which can also be updated.
2. The calculations to the RowNum would then have to be in the C/CPP 
application which would nullify the use of customizable HTML template.

Have a dynamite day.

Michael Henshaw
AdCom Technologies Inc.
1119 Superior Ave.
Sheboygan, WI 53081
(920) 694-0039

http://www.adcomtechnologies.com/

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Igor Tandetnik
Sent: Monday, August 09, 2010 5:25 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Incremental row number associated to the current Query?

Mike Henshaw <mhens...@adcomtechnologies.com> wrote:
> Is there a way to create an incremental row or show a row number that is 
> linked to the current select query that can be used in
> calculations? 
> 
> Basically a row counter for the current query that can be used in 
> calculations.

Your application makes a series of sqlite3_step calls, one for each row. Can't 
it just increment a counter on each step?

> 2. The application is written in C/CPP so no counters or variables from C/CPP 
> can be used.

I don't see how the second statement follows from the first. In fact, it 
appears that just the opposite should be the case.
-- 
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] Incremental row number associated to the current Query?

[Igor Tandetnik]

Mike Henshaw  wrote:
> Is there a way to create an incremental row or show a row number that is 
> linked to the current select query that can be used in
> calculations? 
> 
> Basically a row counter for the current query that can be used in 
> calculations.

Your application makes a series of sqlite3_step calls, one for each row. Can't 
it just increment a counter on each step?

> 2. The application is written in C/CPP so no counters or variables from C/CPP 
> can be used.

I don't see how the second statement follows from the first. In fact, it 
appears that just the opposite should be the case.
-- 
Igor Tandetnik


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

[sqlite] Incremental row number associated to the current Query?

[Mike Henshaw]

Is there a way to create an incremental row or show a row number that is linked 
to the current select query that can be used in calculations?

Basically a row counter for the current query that can be used in calculations.

Notes:
1. The query can be change at any time.
2. The application is written in C/CPP so no counters or variables from C/CPP 
can be used.
3. The device has limited flash memory so a scripting language such as Perl, 
PHP, or Ruby cannot be used.
4. The table primary key are not always in consecutive order (useless records 
that have no historical significant are removed).

Query:
SELECT
SomeSortOfRowCount AS RowNum,
COALESCE(Locations.Name, Equipment.Area, 'Unkown Location') AS 
LocalName,
COALESCE('(' || Employee.FirstName || ' ' || Employee.LastName || ')', 
'') AS Employee,
Equipment.SerialNum AS SerialNo,
REPLACE(COALESCE(Equipment.Area || ' ' || EquipmentUses.Name, 
Equipment.EquipmentTypeName), 'SVR', 'Server') AS Item,
strftime('%m-%d-%Y',MIN(EquipmentHistory.DateTimeOccured)) AS DateAdded
FROM Equipment
LEFT OUTER JOIN Employee ON Employee.EmployeeId = Equipment.EmployeeId
LEFT OUTER JOIN Locations ON Locations.LocationId = Equipment.LocationId OR 
Locations.LocationId = Employee.LocationId
LEFT OUTER JOIN EquipmentUses ON EquipmentUses.EquipmentUsesId = 
Equipment.EquipmentUsesId
LEFT OUTER JOIN EquipmentHistory ON EquipmentHistory.SerialNum = 
Equipment.SerialNum
WHERE Equipment.SerialNum <> ""
GROUP BY Equipment.SerialNum
ORDER BY LocalName


Output:
RowNum | LocalName| Employee| SerialNo   | Item  | 
DateAdded
---
1  | Bob's Work Bench | (Bob Smith) | #S444SSABA | Hammer Drill  | 
2010-10-10
2  | Tom's Work Bench | (Tom Smith) | #BAA890293 | Desk Phone| 
2010-10-10
3  | Front Desk   |   | #203WE3902 | Local Workstation | 
2010-10-10

OR

Query:
SELECT
(SomeSortOfRowCount % 2) + 1 AS RowNum,
COALESCE(Locations.Name, Equipment.Area, 'Unkown Location') AS 
LocalName,
COALESCE('(' || Employee.FirstName || ' ' || Employee.LastName || ')', 
'') AS Employee,
Equipment.SerialNum AS SerialNo,
REPLACE(COALESCE(Equipment.Area || ' ' || EquipmentUses.Name, 
Equipment.EquipmentTypeName), 'SVR', 'Server') AS Item,
strftime('%m-%d-%Y',MIN(EquipmentHistory.DateTimeOccured)) AS DateAdded
FROM Equipment
LEFT OUTER JOIN Employee ON Employee.EmployeeId = Equipment.EmployeeId
LEFT OUTER JOIN Locations ON Locations.LocationId = Equipment.LocationId OR 
Locations.LocationId = Employee.LocationId
LEFT OUTER JOIN EquipmentUses ON EquipmentUses.EquipmentUsesId = 
Equipment.EquipmentUsesId
LEFT OUTER JOIN EquipmentHistory ON EquipmentHistory.SerialNum = 
Equipment.SerialNum
WHERE Equipment.SerialNum <> ""
GROUP BY Equipment.SerialNum
ORDER BY LocalName

Output:
RowNum | LocalName| Employee| SerialNo   | Item  | 
DateAdded
---
1  | Bob's Work Bench | (Bob Smith) | #S444SSABA | Hammer Drill  | 
2010-10-10
2  | Tom's Work Bench | (Tom Smith) | #BAA890293 | Desk Phone| 
2010-10-10
1  | Front Desk   |   | #203WE3902 | Local Workstation | 
2010-10-10


I have tried:
SELECT
(SELECT COUNT(*) FROM Equipment tb1 WHERE tb1. EquipmentId <= 
tb2.EquipmentId AND tb1. SerialNum <> "") AS RowNum,
COALESCE(Locations.Name, tb2.Area, 'Unkown Location') AS LocalName,
COALESCE('(' || Employee.FirstName || ' ' || Employee.LastName || ')', 
'') AS Employee,
tb2.SerialNum AS SerialNo,
REPLACE(COALESCE(tb2.Area || ' ' || EquipmentUses.Name, 
tb2.EquipmentTypeName), 'SVR', 'Server') AS Item,
strftime('%m-%d-%Y',MIN(EquipmentHistory.DateTimeOccured)) AS DateAdded
FROM Equipment tb2
LEFT OUTER JOIN Employee ON Employee.EmployeeId = tb2.EmployeeId
LEFT OUTER JOIN Locations ON Locations.LocationId = tb2.LocationId OR 
Locations.LocationId = Employee.LocationId
LEFT OUTER JOIN EquipmentUses ON EquipmentUses.EquipmentUsesId = 
tb2.EquipmentUsesId
LEFT OUTER JOIN EquipmentHistory ON EquipmentHistory.SerialNum = tb2.SerialNum
WHERE tb2.SerialNum <> ""
GROUP BY tb2.SerialNum
ORDER BY LocalName

But:
1. The row numbers / counter is not in the proper order (ORDER BY LocalName).
2. Other queries that use a row number / counter will COUNT(*) using GROUP BY 
ROW_X which then RowNum will count every instance where the ROW is not NULL.

The only way I have success was to create a temp table:
1. Drop the table (table_query_temp) if it exits.
2. Create a Table (table_query_temp) with an incremental Primary Key.
3. Run the query as a insert (insert into table_query_temp (...) select ) 
while leaving the Primary Key as null for inserts.
4. Query the new table (select * from table_query_temp).
5. Drop the table