Search This Blog

Tuesday, December 8, 2015

Unexpected results Min() Max() results from SELECT TOP 100 sql statement

Testing a solution to find min() max() of 100 rows returned from a query provided some interesting results.

SELECT  min(x.radar_queue_key), max(x.radar_queue_key)
FROM (SELECT TOP 100 radar_queue_key FROM z_radar_queue WHERE radar_group_Code='SFV OTS' ORDER BY radar_queue_key asc) as x

Results:867 966

SELECT  min(x.radar_queue_key), max(x.radar_queue_key)
FROM (SELECT TOP 100 radar_queue_key FROM z_radar_queue WHERE radar_group_Code='SFV OTS') as x

Results:867 1879

SELECT  min(radar_queue_key), max(radar_queue_key)
FROM  z_radar_queue 
WHERE radar_group_Code='SFV OTS'

867 1879


What is interesting is that actual Min() =867 and actual max() =966 for the Top 100 rows.

Both of these queries return exactly the same rows in exactly the same order (even when flushing data cache) because radar_queue_key is the primary key on the table.



SELECT TOP 100 radar_queue_key FROM z_radar_queue WHERE radar_group_Code='SFV OTS' ORDER BY radar_queue_key asc

SELECT TOP 100 radar_queue_key FROM z_radar_queue WHERE radar_group_Code='SFV OTS'

Value of 1879 does not appear in either result set. The execution is completely different if the sql statements are used as a subquery.

Lesson here is always use an order by clause when fetching rows for aggregation + TOP xxxx

Saturday, October 31, 2015

Setting up SQLite and EF6

Here is something that is more difficult than it should be.

My answer on StackOverflow:

My environment:

VS2013 Pro
Entity Framework 6: version 6.1.3
Sqlite .net library: System.Data.SQLite versions: 1.093 - 1.095

Do not use Nuget Manager to download 



Monday, October 26, 2015

How to efficently update WPF MainWindow from an async task

Is this an acceptable approach to update WPF MainWindow from an async task, using events thrown from a public static class?
In MainWindow.cs, I subscribe to UI.TaskCompleted event In UI.cs, I subscribe to AsyncTaskActions.TaskCompleted event.
Using this pattern, AsyncTaskActions.TaskCompleted is raised with a async task is completed. UI catch the event and raise UI.TaskCompleted. This way, the event is catch in MainWindow code where I can use Displacher.Invoke to refresh the displayed page. The end result is I get a page refresh when a task is completed and the task still runs asychronously.
Application design summary:
MainWindow: main window which can have several different page classes in content area within the main window.
Common.cs public static class contains a number of common methods through the UI in the application.
AsyncTaskActions.cs - class with a number of async common methods (ie download file)



Tuesday, August 25, 2015

Useful sqlite system queries


List tables and indexes in SQlite database:

SELECT * FROM sqlite_master


List columns for a table:

pragma table_info()

Monday, July 27, 2015

Amazon File Hosting

Starting research again for a proper secured download application site.

Starting here:

https://css-tricks.com/how-to-set-up-and-host-a-publicly-accessible-file-on-amazon-s3-simple-storage-service/


Saturday, July 4, 2015

NSIS 3.0b1 setup for DRC

Installing NSIS on HP laptop with the following steps

Files in SVN  ..,/svn/DRC_Installer/References

http://nsis.sourceforge.net/Main_Page
http://www.spaceblue.com/products/venis/
My copy of Venis

1) Install nsis-3.0b1-setup.exe
2) Install Venis Editor
3) Install Registry.zip Addin  (Optional)
    a) Within Registry.zip copy:
          - Desktop\example  => c:\Program Files(x86)\NSIS\Examples\
          - Desktop\Include => c:\Program Files(x86)\NSIS\Include\
          - Desktop\Plugin => c:\Program Files(x86)\NSIS\Plugins\
4) Copy ZipDLL.dll to  c:\Program Files(x86)\NSIS\Plugins\x86-ansi\
5) Copy nsProcess.nsh c:\Program Files(x86)\NSIS\Include\
6) Copy ZipDLL.dll to C:\Program Files (x86)\NSIS\Plugins\x86-ansi\