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 (table_query_temp).
The only problems using a temp table are:
1. This is on an embedded device that will be using a SD card. And the queries
will be run by a user.
2. Read and write speeds will be slow to the storage drive.
3. The more useless read and writes to the storage device the quicker the SD
card will fail. Right now some of the tables are stored in memory which
reduces the read and writes to the SD card.
4. The same query might be ran at the same time by different users.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
