-- set this up on web page so I could copy/paste
-- to my android...
-- create database bugs;
-- use bugs;
DROP TABLE IF EXISTS Accounts;
DROP TABLE IF EXISTS BugStatus;
DROP TABLE IF EXISTS Bugs;
DROP TABLE IF EXISTS Comments;
DROP TABLE IF EXISTS Screenshots;
DROP TABLE IF EXISTS Tags;
DROP TABLE IF EXISTS Products;
DROP TABLE IF EXISTS BugsProducts;
#START:create
CREATE TABLE Accounts (
account_id SERIAL PRIMARY KEY,
account_name VARCHAR(20),
first_name VARCHAR(20),
last_name VARCHAR(20),
email VARCHAR(100),
password_hash CHAR(64),
portrait_image BLOB,
hourly_rate NUMERIC(9,2)
);
CREATE TABLE BugStatus (
status VARCHAR(20) PRIMARY KEY
);
CREATE TABLE Bugs (
bug_id SERIAL PRIMARY KEY,
date_reported DATE NOT NULL,
summary VARCHAR(80),
description VARCHAR(1000),
resolution VARCHAR(1000),
reported_by BIGINT UNSIGNED NOT NULL,
assigned_to BIGINT UNSIGNED,
verified_by BIGINT UNSIGNED,
status VARCHAR(20) NOT NULL DEFAULT 'NEW',
priority VARCHAR(20),
hours NUMERIC(9,2),
FOREIGN KEY (reported_by) REFERENCES Accounts(account_id),
FOREIGN KEY (assigned_to) REFERENCES Accounts(account_id),
FOREIGN KEY (verified_by) REFERENCES Accounts(account_id),
FOREIGN KEY (status) REFERENCES BugStatus(status)
);
CREATE TABLE Comments (
comment_id SERIAL PRIMARY KEY,
bug_id BIGINT UNSIGNED NOT NULL,
author BIGINT UNSIGNED NOT NULL,
comment_date DATETIME NOT NULL,
comment TEXT NOT NULL,
FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id),
FOREIGN KEY (author) REFERENCES Accounts(account_id)
);
CREATE TABLE Screenshots (
bug_id BIGINT UNSIGNED NOT NULL,
image_id BIGINT UNSIGNED NOT NULL,
screenshot_image BLOB,
caption VARCHAR(100),
PRIMARY KEY (bug_id, image_id),
FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id)
);
CREATE TABLE Tags (
bug_id BIGINT UNSIGNED NOT NULL,
tag VARCHAR(20) NOT NULL,
PRIMARY KEY (bug_id, tag),
FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id)
);
CREATE TABLE Products (
product_id SERIAL PRIMARY KEY,
product_name VARCHAR(50)
);
CREATE TABLE BugsProducts(
bug_id BIGINT UNSIGNED NOT NULL,
product_id BIGINT UNSIGNED NOT NULL,
PRIMARY KEY (bug_id, product_id),
FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id),
FOREIGN KEY (product_id) REFERENCES Products(product_id)
);
#END:create
INSERT INTO Accounts () VALUES ();
INSERT INTO Products (product_id, product_name) VALUES
(1, 'Open RoundFile'),
(2, 'Visual TurboBuilder'),
(3, 'ReConsider');
INSERT INTO Bugs (bug_id, summary) VALUES
(1234, 'crash when I save'),
(2345, 'increase performance'),
(3456, 'screen goes blank'),
(5678, 'unknown conflict between products');
INSERT INTO BugsProducts (bug_id, product_id) VALUES
(1234, 1),
(1234, 3),
(3456, 2),
(5678, 1),
(5678, 3);
INSERT INTO Comments (comment_id, bug_id, comment) VALUES
(6789, 1234, 'It crashes!'),
(9876, 2345, 'Great idea!');
INSERT INTO Tags () VALUES ();
show_r.ksh
Handy Subversion script. Pass it a revision number and it shows the login comment ~ related
files.
files.
xxx::/home/mbxxxxx/apps/datamodules/datamodules/trunk> . _CMD/show_r.ksh 114
------------------------------------------------------------------------
r114 | mbee | 2013-12-02 14:34:30 -0600 (Mon, 02 Dec 2013) | 1 line
null matrix id
------------------------------------------------------------------------
...
114 mbee 690 Dec 02 14:34 _CMD/process_null_matrix_ids.ksh
114 mbee 9527 Dec 02 14:34 _CMD/pull_push_null_matrix_id_updates.ksh
114 mbee 8553 Dec 02 14:34 _CMD/pull_rollback_of_matrix_id_updates.ksh
xxx::/home/mbxxxx/apps/datamodules/datamodules/trunk> cat _CMD/show_r.ksh
svn log -r $1
svn list -v -r $1 --depth=infinity|grep "$1 "
Copy_2_ro_folder.vbs
This copies a powerpivot model from it's source to a Read-Only target. The slicers and other pivot interactions still work the same -- but, users can't alter the sheet (directly).
Using PowerWindow table properties vs refresh...
I find using 'table properties' a better option for refreshing tables in the power window. Some of the tables are quite large -- and, some of our models have a lot of tables. Using table properties allows me to see _when_ a table was last updated; and, then, if choose to, I can _save_ to refresh the table.
I'm investigating a way to capture this window information to add as a .csv imported table.
Talking Clipboard (for Android)
This is an ok tool. If you've some long text to get through - copy to the clipboard and it can read it back to you. I find this helpful.
The program has a few quirks: it doesn't appear in your list of running apps - and there's no direct way to stop it (short of rebooting your device).
However, even so it's handy you can give it a webpage to read back to you or it monitors the clipboard and can read what you copy to it.
Safari books online - download if you prefer
You can download material in epub and have it readable in your FireFox or SeaMonkey web browser. I much prefer this. I notice too, that my Android will bring up the epub in my favorite book reader -- moonreader.
Highlights from : From Scrum Shortcuts without Cutting Corners/Agile Tactics, Tools, & Tips / Ilan Goldstein...
Sometimes, I like reading from the end of the story('a snark was a boojum you see'). This book appears to have come from introspection after having finished many a game. Start anywhere. It's a non-sequential smorgasbord - worth your time. It will help from making false starts/mis-fires into scrum.
Google it
What they say on goodreads
Highlights
Google it
What they say on goodreads
Highlights
Shortcut 2: Fragile Agile /
Possibly one of the most frustrating comments that I hear when speaking to novice software teams is, “We do Scrum—we work in sprints, we have a daily scrum, and we even have a product backlog.” In addition, although they may not explicitly say it, you can often add, “We don’t write any documentation, we release haphazardly, we plan on the fly, and we don’t care about buggy code because we’ll just fix it up with a bug iteration.” ARGH! These people give Scrum a terrible name, and worse still, when their projects inevitably fail, it is very difficult, if not impossible, to win back the senior stakeholders who have been burnt by a badly warped Scrum implementation.
Possibly one of the most frustrating comments that I hear when speaking to novice software teams is, “We do Scrum—we work in sprints, we have a daily scrum, and we even have a product backlog.” In addition, although they may not explicitly say it, you can often add, “We don’t write any documentation, we release haphazardly, we plan on the fly, and we don’t care about buggy code because we’ll just fix it up with a bug iteration.” ARGH! These people give Scrum a terrible name, and worse still, when their projects inevitably fail, it is very difficult, if not impossible, to win back the senior stakeholders who have been burnt by a badly warped Scrum implementation.
TOChttp://www.agilemanifesto.org/
Chapter 1. Scrum Startup Shortcut 1: Scrum on the Pitch Shortcut 2: Fragile Agile Shortcut 3: Creative Comfort Wrap Up Chapter 2. Attitudes and Abilities Shortcut 4: Masterful ScrumMaster Shortcut 5: Rock Stars or Studio Musicians? Shortcut 6: Picking Your Team Line-Up Wrap Up Chapter 3. Planning and Protecting Shortcut 7: Setting the Scrum Stage Shortcut 8: Plan the Sprint, Sprint the Plan Shortcut 9: Incriminating Impediments Wrap Up Chapter 4. Requirement Refinement Shortcut 10: Structuring Stories Shortcut 11: Developing the Definition of Done Shortcut 12: Progressive Revelations Wrap Up Chapter 5. Establishing Estimates Shortcut 13: Relating to Estimating Shortcut 14: Planning Poker at Pace Shortcut 15: Transitioning Relatively Wrap Up Chapter 6. Questioning Quality Shortcut 16: Bah! Scrum Bug! Shortcut 17: We Still Love the Testers! Shortcut 18: Automation Nation Wrap Up Chapter 7. Monitoring and Metrics Shortcut 19: Metrics That Matter Shortcut 20: Outstanding Stand-Ups Shortcut 21: Taming the Task Board Wrap Up Chapter 8. Retros, Reviews, and Risks Shortcut 22: To-Dos for Your Sprint Reviews Shortcut 23: Retrospective Irrespective Shortcut 24: Risk Takers and Mistake Makers Wrap Up Chapter 9. Managing the Managers Shortcut 25: Perception Is Reality Shortcut 26: Our Lords and Masters Shortcut 27: Morphing Managers in the Matrix Wrap Up Chapter 10. Larger Lessons Shortcut 28: Scrum Rollout Reckoning Shortcut 29: Eyes on the Prize Shortcut 30: Shortcut to the Final Level Final Wrap Up References
How to get to 'Advanced' tab in power pivot window:
When in PowerPivot window - click on the icon left/top under titlebar-- there should be a drop down that allows you to switch to the 'advanced mode' view tab. I find this most useful for the managing 'perspectives'. I create two perspectives 'CORE' for tables that require regular updates and 'LKUP' for those tables that remain mostly static over time. I then use AutoIt scripts to refresh the 'CORE' tables.
How-to PowerPivot (sans Sharepoint)
Without Sharepoint refreshing powerpivot sheets is an
onerous task. To take some of the sting/pain out of it
here's what I'm presently doing:
1) Setup DSN's locally (in my case MySQL with UID/PWD)
2) Setup CORE and LKUP perspectives (see advanced mode tab):
-CORE are core data tables that are used regularly
-LKUP are static (more or less) tables used in relations
-Both are lined up horizontally in each perspective
3) Create a series of AutoIt executables to:
- Open a passed powerpivot sheet (via a pivotxls var)
- Go to powerpivot window
- Go to diagram view choose 'CORE' view
- Refresh one or more tables(via a passed notabs variable)
- Save main sheet
4) Join the smaller executables in #3 into a single AutoIt executable call it RefPPivot.exe)
5) Now I have a script that functions in the form:
EXAMPLE:
...would refresh the P:\accounting\zPowerPivot\myPowerStuff.xlsx's
8 core tables.
6) Schedule #5 through myLauncher.hta
This works tolerably well. I created another, pretty much identical script to #4 but use for LKUP updates(RefPPivotLKUP.exe).
KEY: When creating / building connections *don't* supply the uid/pwd - assign them rather in the DSN itself. This works well for mySQL -- but I think this may work for some other DB's as well (like Oracle). When you get to the 'next' prompt to create table / query - first chose the 'All' tab and persist the security info.
I have samples scripts and further suggestions if anyone's interested.
ADDITIONAL: If you're trying to see if a powerpivot table has been updated go to the powerwindow / design view then click table properties - should be in red/magenta in lower right corner. I usually check the last table in my 'CORE' list -- assume that the ones prior in the list bear the same or slightly earlier date/time.
I use table-properties so often I found it helpful to put that as well as a few other commands on the quick-access toolbar:
1) Setup DSN's locally (in my case MySQL with UID/PWD)
2) Setup CORE and LKUP perspectives (see advanced mode tab):
-CORE are core data tables that are used regularly
-LKUP are static (more or less) tables used in relations
-Both are lined up horizontally in each perspective
3) Create a series of AutoIt executables to:
- Open a passed powerpivot sheet (via a pivotxls var)
- Go to powerpivot window
- Go to diagram view choose 'CORE' view
- Refresh one or more tables(via a passed notabs variable)
- Save main sheet
4) Join the smaller executables in #3 into a single AutoIt executable call it RefPPivot.exe)
5) Now I have a script that functions in the form:
RefPPivot.exe pivotxlsx notabs
RefPPivot.exe P:\accounting\zPowerPivot\myPowerStuff.xlsx 8
...would refresh the P:\accounting\zPowerPivot\myPowerStuff.xlsx's
8 core tables.
6) Schedule #5 through myLauncher.hta
This works tolerably well. I created another, pretty much identical script to #4 but use for LKUP updates(RefPPivotLKUP.exe).
KEY: When creating / building connections *don't* supply the uid/pwd - assign them rather in the DSN itself. This works well for mySQL -- but I think this may work for some other DB's as well (like Oracle). When you get to the 'next' prompt to create table / query - first chose the 'All' tab and persist the security info.
I have samples scripts and further suggestions if anyone's interested.
ADDITIONAL: If you're trying to see if a powerpivot table has been updated go to the powerwindow / design view then click table properties - should be in red/magenta in lower right corner. I usually check the last table in my 'CORE' list -- assume that the ones prior in the list bear the same or slightly earlier date/time.
I use table-properties so often I found it helpful to put that as well as a few other commands on the quick-access toolbar:
Sample PowerPivot.xlsx and PowerWindow |
sqlpocket mysql
-- - -- This is the mysql version of the database used with: -- sql pocket guide 3rd ed. by Jonathan Gennick -- ...I placed here so I could copy/paste into KSWEB -- - -- drop database if exists sqlpocket; create database sqlpocket; use sqlpocket; create table gov_unit ( id integer NOT NULL, parent_id integer DEFAULT 3, name varchar(10), type varchar(8), constraint gov_unit_pk primary key (id), constraint gov_unit_type_chk check (type in ('County','Township','City','State')), constraint gov_unit_loop foreign key (parent_id) references gov_unit(id) ) engine=innodb; create table township ( id integer, name varchar(10), population integer ) engine=innodb; create view small_township as select * from township where population < 500; create table county ( id integer not null, name varchar(10), population integer, constraint county_pk primary key (id) ) engine=innodb; create table state ( id integer, name varchar(10), population integer ) engine=innodb; create table other_unit ( id integer, name varchar(10), population integer ) engine=innodb; create table owner ( id integer not null, name varchar(15), phone varchar(12), type varchar(7), constraint owner_pk primary key (id), constraint owner_type_chk check (type in ('public','private')) ) engine=innodb; create table upfall ( id integer not null, name varchar(15), datum varchar(7), zone integer, northing integer, easting integer, lat_lon varchar(20), county_id integer, open_to_public varchar(1), owner_id integer, description varchar(80), confirmed_date timestamp, constraint upfalls_pk primary key (id), constraint open_to_public_chk check (open_to_public in ('y','n')), constraint owner_id_fk foreign key (owner_id) references owner (id), constraint gov_unit_id foreign key (county_id) references county (id) ) engine=innodb; create table fall_location ( id integer, datum varchar(7), zone integer, northing integer, easting integer ) engine=innodb; create table fall_description ( id integer, name varchar(15), description varchar(80) ) engine=innodb; create table new_falls ( id integer, name varchar(15), datum varchar(7), zone integer, northing integer, easting integer, lat_lon varchar(20), county_id integer, open_to_public varchar(1), owner_id integer, description varchar(80), confirmed_date timestamp ) engine=innodb; create table trip ( name varchar(10) not null, stop integer not null, parent_stop integer, constraint trips_pk primary key (name, stop), constraint parent_stop_fk foreign key (name, parent_stop) references trip (name, stop), constraint stop_fk foreign key (stop) references upfall (id) ) engine=innodb; CREATE TABLE pivot ( x integer ) engine=innodb; CREATE TABLE parent_example ( name varchar(15) not null, country varchar(2) not null DEFAULT 'CA', CONSTRAINT par_example_ctry CHECK (country IN ('CA','US')), CONSTRAINT parent_example_pk PRIMARY KEY(name, country) ) engine=innodb; create table duel ( dummy varchar(1) ); insert into duel values ('X'); insert into gov_unit values (3,null,'Michigan','State'); insert into gov_unit values (2,3,'Alger','County'); insert into gov_unit values (1,2,'Munising','City'); insert into gov_unit values (4,2,'Munising','Township'); insert into gov_unit values (5,2,'Au Train','Township'); insert into gov_unit values (6,3,'Baraga','County'); insert into gov_unit values (7,3,'Ontonagon','County'); insert into gov_unit values (8,7,'Interior','Township'); insert into gov_unit values (9,3,'Dickinson','County'); insert into gov_unit values (10,3,'Gogebic','County'); insert into gov_unit values (11,3,'Delta','County'); insert into gov_unit values (12,11,'Masonville','Township'); insert into township values (4,'Munising',3125); insert into township values (5,'Au Train',1172); insert into township values (8,'Interior',375); insert into township values (12,'Masonville',1877); insert into county values (2,'Alger',9862); insert into county values (6,'Baraga',8746); insert into county values (7,'Ontonagon',7818); insert into county values (9,'Dickinson',27472); insert into county values (10,'Gogebic',17370); insert into county values (11,'Delta',38520); insert into state values (20, 'Montana', 902195); insert into state values (21, 'N. Dakota', null); insert into state values (3, 'Michigan', 9938444); insert into owner values (1,'Pictured Rocks','906.387.2607','public'); insert into owner values (2,'Michigan Nature','517.655.5655','private'); insert into owner values (3,'AF LLC',null,'private'); insert into owner values (4,'MI DNR','906-228-6561','public'); insert into owner values (5,'Horseshoe Falls','906.387.2635','private'); insert into upfall values (1,'Munising Falls','NAD1927',16,5141184,0528971,null,2,'y',1, 'Munising''s namesake waterfall; the one on all the postcards', timestamp '2005-12-08 19:30:00'); insert into upfall values (2,'Tannery Falls','NAD1927',16,5140000,0528808,null,2,'y',2, 'Like Munising Falls, but nicely undeveloped', timestamp '2005-12-08 19:50:00'); insert into upfall values (3,'Alger Falls','NAD1927',16,5137795,0527046,null,2,'y',3, 'Nice falls at east entrance into town', timestamp '2005-12-08 20:03:00'); insert into upfall values (4,'Wagner Falls','NAD1927',16,5137310,0526989,null,2,'y',4, 'Small, scenic falls near intersection of M-28 and M-94', timestamp '2005-12-08 20:04:00'); insert into upfall values (5,'Horseshoe Falls','NAD1927',16,5138877,0527323,null,2,'y',null, 'Falls, garden, scenic walk, and a fish pond. Kids can feed the fish.', timestamp '2005-12-08 20:07:00'); insert into upfall values (6,'Miners Falls',null,null,null,null,null,2,'y',1, 'Highest waterflow in the Pictured Rocks.', timestamp '2005-12-08 20:10:00'); insert into upfall values (7,'Little Miners',null,null,null,null,null,2,'y',1, 'Scenic, must bushwhack two miles off the trail, on Little Miners River', timestamp '2005-12-08 20:15:00'); insert into upfall values (8,'Scott Falls',null,null,null,null,null,2,'y',null, 'Scenic roadside falls near Face on the Rock', timestamp '2005-12-18 21:28:00'); insert into upfall values (9,'Canyon Falls',null,null,null,null,null,6,'y',null, 'A few miles north of M-28 (two miles south of Alberta), but worth it', timestamp '2005-12-18 21:35:00'); insert into upfall values (10,'Agate Falls',null,null,null,null,null,7,'y',null, 'Across road from Joseph F. Oravec Roadside Park', timestamp '2005-12-18 21:35:00'); insert into upfall values (11,'Bond Falls',null,null,null,null,null,7,'y',null, 'Bond Falls Basin, nine miles south of Bruce Crossing', timestamp '2005-12-18 21:50:00'); insert into upfall values (12,'Fumee Falls',null,null,null,null,null,9,'y',null, 'Start from Helen Z. Lien Roadside Park 3 1/2 miles west of Norway', timestamp '2005-12-18 21:56:30'); insert into upfall values (13,'Kakabika Falls',null,null,null,null,null,10,'y',null, '1/2 mile north of US-2 off of county road 527', timestamp '2005-12-18 22:03:09'); insert into upfall values (14,'Rapid River Fls',null,null,null,null,null,11,'y',null, 'Park includes playground, barbecue pits, flowing well, picnic tables', timestamp '2005-12-18 22:13:11'); insert into upfall values (30,'Twin Falls #1','NAD1927',16,5140500,0528641,null,2,'y',2, 'Near Twin Falls #2', timestamp '2006-02-10 19:35:00'); insert into upfall values (31,'Twin Falls #2','NAD1927',16,5140500,0528663,null,2,'y',2, 'Near Twin Falls #1', timestamp '2006-02-10 19:35:00'); insert into new_falls values (2,'Olson Falls',null,null,null,null,null,2,'y',2, 'Like Munising Falls, but nicely undeveloped', timestamp '2005-12-08 19:50:00'); insert into new_falls values (20,'MNA Memorial',null,null,null,null,null,2,'y',2, 'Right near Tannery Falls', timestamp '2006-01-31 09:54:00'); insert into trip values ('Munising',1,null); insert into trip values ('Munising',2,1); insert into trip values ('Munising',6,2); insert into trip values ('Munising',4,6); insert into trip values ('Munising',3,4); insert into trip values ('Munising',5,3); insert into trip values ('M-28',3,null); insert into trip values ('M-28',1,3); insert into trip values ('M-28',8,1); insert into trip values ('M-28',9,8); insert into trip values ('M-28',10,9); insert into trip values ('M-28',11,10); insert into trip values ('US-2',14,null); insert into trip values ('US-2',12,14); insert into trip values ('US-2',11,12); insert into trip values ('US-2',13,11); INSERT INTO pivot SELECT huns.x+tens.x+ones.x FROM (SELECT 0 x FROM dual UNION ALL SELECT 1 x FROM dual UNION ALL SELECT 2 x FROM dual UNION ALL SELECT 3 x FROM dual UNION ALL SELECT 4 x FROM dual UNION ALL SELECT 5 x FROM dual UNION ALL SELECT 6 x FROM dual UNION ALL SELECT 7 x FROM dual UNION ALL SELECT 8 x FROM dual UNION ALL SELECT 9 x FROM dual) ones, (SELECT 0 x FROM dual UNION ALL SELECT 10 x FROM dual UNION ALL SELECT 20 x FROM dual UNION ALL SELECT 30 x FROM dual UNION ALL SELECT 40 x FROM dual UNION ALL SELECT 50 x FROM dual UNION ALL SELECT 60 x FROM dual UNION ALL SELECT 70 x FROM dual UNION ALL SELECT 80 x FROM dual UNION ALL SELECT 90 x FROM dual) tens, (SELECT 0 x FROM dual UNION ALL SELECT 100 x FROM dual UNION ALL SELECT 200 x FROM dual UNION ALL SELECT 300 x FROM dual UNION ALL SELECT 400 x FROM dual UNION ALL SELECT 500 x FROM dual UNION ALL SELECT 600 x FROM dual UNION ALL SELECT 700 x FROM dual UNION ALL SELECT 800 x FROM dual UNION ALL SELECT 900 x FROM dual) huns; COMMIT;
mySQL for Android
I was going through a SQL text and was muttering to myself. It sure would be nice if there was an Android version of mySQL I could use with these examples on my tablet.
Guess what? There is -- it works!
https://play.google.com/store/apps/details?id=ru.kslabs.ksweb&hl=en
Guess what? There is -- it works!
https://play.google.com/store/apps/details?id=ru.kslabs.ksweb&hl=en
wait_while_jobs_are_running.ksh
When running a number of jobs in the background -- I run this in the foreground:
::/apps/datamodules/_SQL> jobs
::/apps/datamodules/_SQL> cat ../_CMD/wait_while_jobs_are_running.ksh
export jr=`jobs|grep -c "Running"`
while [[ $jr -ne 0 ]];do
sleep 30
export jr=`jobs|grep -c "Running"`
done
...it basically sleeps 30 seconds until there are no more running jobs.
::/apps/datamodules/_SQL> jobs
::/apps/datamodules/_SQL> cat ../_CMD/wait_while_jobs_are_running.ksh
export jr=`jobs|grep -c "Running"`
while [[ $jr -ne 0 ]];do
sleep 30
export jr=`jobs|grep -c "Running"`
done
...it basically sleeps 30 seconds until there are no more running jobs.
UnDup SQL pattern
/*
|| Recording this here for reference
|| it's a pattern that will repeat
|| In theory you can do without creating
|| interim table -- in practice I've found
|| otherwise...
|| 'min_record_id' is assumed to be a
|| unique sequential number assigned to
|| records as they come in ...
*/
CREATE TABLE min_record_id (record_id bigint(19));
CREATE UNIQUE INDEX min_record_id_PK on min_record_id(record_id);
INSERT INTO min_record_id
SELECT *
FROM (-- ilv
SELECT MIN(record_id) as record_id
FROM dup_file
GROUP
BY {uniquely identifying items}
)
ilv
;
CREATE TABLE file_unique
SELECT df.*
FROM dup_file df
inner join
min_record_id mri
on df.record_id
= mri.record_id
;
DROP TABLE min_record_id;
|| Recording this here for reference
|| it's a pattern that will repeat
|| In theory you can do without creating
|| interim table -- in practice I've found
|| otherwise...
|| 'min_record_id' is assumed to be a
|| unique sequential number assigned to
|| records as they come in ...
*/
CREATE TABLE min_record_id (record_id bigint(19));
CREATE UNIQUE INDEX min_record_id_PK on min_record_id(record_id);
INSERT INTO min_record_id
SELECT *
FROM (-- ilv
SELECT MIN(record_id) as record_id
FROM dup_file
GROUP
BY {uniquely identifying items}
)
ilv
;
CREATE TABLE file_unique
SELECT df.*
FROM dup_file df
inner join
min_record_id mri
on df.record_id
= mri.record_id
;
DROP TABLE min_record_id;
#Powerpivot~look at datasource on xlsx sheet -- s/b 'powerpivot data' -- keep drilling down to see last refreshed.
#Powerpivot~look at datasource on xlsx sheet -- s/b 'powerpivot data' -- keep drilling down to see last refreshed. |
Subscribe to:
Posts (Atom)