Tuesday, June 9, 2020

Midsummer Midsomer Murder SQL Challenge and Book Competition

It's getting close to Midsummer, where in Denmark we traditionally burn witches on Saint John's Eve.
If we misspell it slightly to Midsomer, we have the Midsomer Murders series with loads of dead.

So let's have a little murderous SQL challenge for Midsummer to give you all a chance to win another signed copy of Practical Oracle SQL.

Murdered potato


UPDATE 2020-07-01: See the winner here:
https://www.kibeha.dk/2020/07/midsummer-midsomer-sql-winner.html

Back in April I had a little book raffle using a workout on Oracle Dev Gym, which Hildo won.
This time I'll let you be creative and create SQL to answer a little challenge in order to win the book.

The basis of the challenge is data about body counts in episodes of the TV series Midsomer Murders.
(To fit the challenge, I've edited the data and left out entries like 'Numerous villagers of Midsomer Oaks' so that I only have single persons.)

The murderous data


The data is in the form of XML (actually an HTML table, somewhat edited to be easily usable as XML.) 

<table>

<tr>
<td>Ep ID</td>
<td>Title</td>
<td>Murdered During Episode</td>
<td>Murdered Prior to Episode</td>
<td>Death by Suicide</td>
<td>Accidental or Natural Death</td>
<td>Attempted Murders or Suicides</td>
</tr>
<tr>
<td>0.1</td>
<td>The Killings at Badger's Drift</td>
<td>Emily Simpson|Dennis Rainbird|Iris Rainbird|Katherine Lacey</td>
<td>Bella Trace</td>
<td>Phyllis Cadell|Michael Lacey</td>
<td>Mr. Lacey|Mrs. Lacey</td>
<td></td>
</tr>
<tr>
<td>1.1</td>
<td>Written in Blood</td>
<td>Gerald Hadleigh|Max Jennings</td>
<td>Mr. Hanlon</td>
<td></td>
<td>Ralph Lyddiard|Honoria Lyddiard</td>
<td>Amy Lyddiard</td>
</tr>
{... continues for about 24K ... }

Specifics of the XML:
  • A single root element called table.
    • Containing many tr elements (rows) - one for each episode.
      • Containing 7 td elements (columns) per tr element.
  • The 7 td elements (columns) are:
    • Episode ID in the format {season}.{episode}.
    • Episode title.
    • Names of persons murdered during the episode.
    • Names of persons murdered before the episode.
    • Names of persons committing successful suicides.
    • Names of persons dying accidental or natural deaths.
    • Names of persons victims of attempted murders or suicides.
  • Where multiple persons are mentioned in one of the "Names of" columns, they are delimited by a pipe sign |.
  • The first tr element (row) is a header row containing column names.

I have created a LiveSQL script:


The script creates a table:
create table midsomer_xml (  
   id        integer primary key  
 , the_xml   clob  
);

And populates the table with a single row with id = 1 and the ~24K XML text in the the_xml column.

The murderous challenge


Your challenge, should you choose to accept it, is to create two SQL statements to give me these outputs:

  • Statement 1: Statistics on body counts for the different acts per season and grand total
  • Statement 2: The distinct list of persons for the different acts per season and grand total.

The details on the desired outputs I show at the end of the post.

The murderous rules


The rules for this little competition is:

  • Solutions must be in the form of a script on LiveSQL.
  • Solutions must contain two statements that query the midsomer_xml table (provided in my LiveSQL script above) for the row with id=1 and produce the two outputs shown at the bottom of this blog post.
  • The column header (first tr element) in the XML should not be in the output, but it is allowed to hard-code which column contains what data (i.e. that the first td element contains episode ID, the second contains title, etc.) rather than dynamically query column header names.
  • The SQL statements may use anything that is available in a standard LiveSQL session, including any publicly available packages that Oracle has installed on LiveSQL.
  • The LiveSQL script should have visibility Unlisted and you must post the URL to the script as a comment to this blogpost - that is your entry in the competition.
    (Note: If you have trouble posting a comment here on Blogger, the reason could be that the comment system requires cross-site tracking (third-party) cookies enabled. Sorry, but I can't change that.)
  • I must be able to run your LiveSQL script without getting errors, and when I run it, it should produce the outputs shown below.
  • Entries must be made before Midnight UTC on Saint John's Eve Tuesday June 23rd 2020.
  • Entries will be judged on correctness (producing the desired outputs) as well as what SQL features is used, the elegance and simplicity of the code, the practicability and potential performance, and how well I like the SQL.
  • I'll be the sole judge and my decision is final.
  • I will evaluate entries during the week following the deadline and announce a winner no later than a week after deadline.
  • The prize will be a copy of Practical Oracle SQL - signed if the winner desires it.

Good luck to all who thinks creating a little murderous Midsummer SQL can be a fun pastime 😎


The murderous output details


  • Statement 1: Statistics on body counts for the different acts per season and grand total
Output in LiveSQL:

SEASONEPISODESACTS_MURDERPERSONS_MURDERACTS_HISTORICALPERSONS_HISTORICALACTS_SUICIDEPERSONS_SUICIDEACTS_ACCIDENTPERSONS_ACCIDENTACTS_ATTEMPTPERSONS_ATTEMPT
014411222200
148822004433
24111155115544
34111133116611
45151522224488
55151511004466
65141411114487
77191922227799
88212100333397
98151522116655
108171711443366
11720201122131399
127161600005544
1382121333312121310
14818184400551611
1561818332216161111
165131300117766
174121233004455
186141411112233
196131322000055
206131322113333
2149911002255
-12631731740402727117117139120

Last line has NULL in column season and is the grand total.

ACTS_* columns are how many individual acts of murder, suicide, etc. were committed.
PERSONS_* columns are how many distinct persons were involved in these individual acts.

Notice that for the 4 acts that results in death, ACTS_* and PERSONS_* are the same.
But ACTS_ATTEMPT and PERSONS_ATTEMPT differ, since there has been multiple attempts on some of the police officers.

  • Statement 2: The distinct list of persons for the different acts per season and grand total.
Output in LiveSQL:

SEASON EPISODESPERSONS_MURDERPERSONS_HISTORICALPERSONS_SUICIDEPERSONS_ACCIDENTPERSONS_ATTEMPT
01Dennis Rainbird, Emily Simpson, Iris Rainbird, Katherine LaceyBella TraceMichael Lacey, Phyllis CadellMr. Lacey, Mrs. Lacey-
14Agnes Gray, Alan Hollingsworth, Brenda Buckley, Esslyn Carmichael, Gerald Hadleigh, Guy Gamelin, Ian Craigie, Max JenningsGaius Quintus, Mr. Hanlon-Honoria Lyddiard, Ralph Lyddiard, Simone's mother, William CarterAmy Lyddiard, Doris Winstanley, Tim Riley
24Anna Santarosa, Carla Constanza, Charles Jennings, David Whitely, Dr. Ian Aycott, Hector Bridges, John Smith, Leonard Pike, Richard Bayly, Simon Fletcher, Tara CavendishCatherine Hamilton, Emily Beavis, Eva Hoffman, Joan Chaplin, Judith AlbistonRev. Stephen WentworthFelix Bryce, Jennifer Bryce, John Lampson, Matthew Draper, Patricia SmithDavid Merrill, Hector Bridges, John Merrill, Robert Cavendish
34Bella Devere, Ben Gurdie, Celia Armstrong, James Tate, Marcia Tranter, Marcus Lowrie, Michael Darrow, Peter Drinkwater, Ron Pringle, Rosemary Furman, Samantha JohnstoneJonathan Lowrie, Mrs. Foster, Simon TranterGrahame TranterDavid MacKillop, Madge Fielding, Michael Weston, Mrs. Mortimer, Muriel Harrap, Ruth WestonSamantha Johnstone
45Dave Cutler, Debbie Shortlands, Elspeth Inkpen-Thomas, Felicity Inkpen-Thomas, Gerald Bennett, Gregory Chambers, Kenneth Gooders, Lloyd Kirby, Mary Mohan, Owen August, Robin Wooliscroft, Simon Reason, Steve Ramsey, Suzanna Chambers, Tristan GoodfellowCynthia Bennett, Karl WainwrightEvelyn Pope, Tammie, the mother of John FieldDave Ripert, Eddie Field, Isabel Aubrey, Ronald StokesAugustus Deverell, Christian Aubrey, Clarice Opperman, Evelyn Pope, Julia Gooders, Naomi Inkpen, Sean O'Connell, WPC Jay Nash
55Adam Keyne, Archie Bellingham, Daniel Talbot, Dudley Carew, Emma Tysoe, Ginny Sharp, Greg Tutt, James Harrington, Lady Lavinia Chetwood, Marcus Steadman, Marjorie Empson, Melissa Townsend, Raif Canning, Susan Bartlett, Victoria BartlettReverend Jonathan Ebbrell-Gerald Empson, Neil Laxton, Reggie Barton, Sir. Walter TalbotFrances Le Bon, Jonah Bloxham, Liam Booker, Peter Fogden, Selwyn Proctor, Sir. Anthony Talbot
65Charles Edmonton, Danny Pinchel, Dr. Duncan Goff, Eddie Darwin, Frank Webster, George Hamilton, Gordon Leesmith, Isobel Hewitt, Julian Shepherd, Keith Scholey, Larry Smith, Martin Wroath, Ruth Fairfax, Tony ParishAndy MooreSt John Smythe-WebsterEllis Bell, Elsie Pinchel, Kenneth Hewitt, Mrs. ScholeyColin Hawksley, DCI Tom Barnaby, DS Gavin Troy, Mallory Edmonton, Mr. Miller, Mrs. Partridge, Phil Harrison
77Alex Deakin, David Heartley-Reade, Dr. Charles Rust, Dr. James Lavery, Fiona Thompson, Gareth Heldman, Jacob Stokes, Jamie Cruickshank, Jennifer Carter, Jezebel Tripp, Jim Hale, Lillian Webster, Liz Francis, Lydia Villiers, Neville Williams, Richard Rackham, Simon Mayfield, Stephen Bannerman, Steven CurtisEric Edwards, Roger HeldmanClaire English, Ferdy VilliersConor Maplin, Lettie Edwards, Michael Bannerman, Mr. Monday, Mrs. Cole, Sir Jonathan Haslett, Susan BannermanAgnes Waterhouse, Alan Clifford, Camilla Crofton, Cully Barnaby, Dr. John Cole, Lydia Villiers, Michael Bannerman, Miriam Heartley-Reade, Sam Callaghan
88Arthur Leggott, Bruce Hartley, Dexter Lockwood, Dr. Gregory Ransom, Dr. John Osgood, Elizabeth Key, Guy Sweetman, Harvey Crane, Henry Plummer, Jack Wilmot, Joanna Craxton, John Ransom, John Whittle, Madeline Villiers, Max Ransom, Mike Spicer, Munro Hilliard, Otto Benham, Owen Swinscoe, Patrick Pennyman, Trevor Machin-David Key, Rosemary Key, Stanley LockwoodColonel Hartley, Joan Alder, Maurice PlummerAntonia Wilmot, DCI Tom Barnaby, DS Dan Scott, Little Mal Kirby, Margaret Winstanley, Peter Craxton, Sandra Tate
98Caroline Cave, Cathy Hewlett, Connor Simpson, Dr. Wellow, Frank Hopkirk, Harriet Davis, Henry York, Lady Sandra Butler, Marion Slade, Mark Castle, Martin Barrett, Mildred Danvers, Peter Cave, Rev. Anthony Gant, Sam JuddFrances Trevelyan, P.C. Colin ArmstrongVivienne MarwoodBella Slade, Giles Southerly, Jean Waverley, Mr. Barrett, Mrs. Barrett, Sir Freddy ButlerAlistair Gooding, Carolyn Armitage, Jack Magwood, Mrs. Beverley, Sir John Waverley
108Alan King, Dr. Alan Delaney, Eddie Marston, Eileen Carnack, Gwen Morrison, Ian King, Jack Colby, Laura Sharp, Lionel Bell, Miles King, Mimi Clifton, Nick Cheyney, Nicky Harding, Rex Masters, Simon Bright, Steve Bright, Tony KirbyMaria GodboldGina Colby, Ginger Foxton, Peter Baxter, Sarah KirkwoodLucinda Thacker, Ralph Wood, Ted HaywardCarol Prentice, Charles King, Dr. James Kirkwood, Ian King, Martin Spellman, Neville Hayward
117Alec Grainger, Aloysius Wilmington, Colin Thomas, Eleanor Crouch, Frederick Tomlin, Guy Sandys, Henry Hammond, Hugo Cartwright, Jack Purdy, James Parkes, Jean Wildacre, Johnny Hammond, Louise Purdy, Lynton Pargeter, Marina Fellowes, Molly Thomas, Peggy Benson, Robin Lawson, Ron Wilson, Terrance LowtherTommy HicksGilly Galsworthy, Mr. PurdyCharlie Finleyson, Colonel Halsey, Cyrus LeVanu, Duggie Hammond, Lady Fitzroy, Libby Wilson, Lord Fitzroy, Lucy Wilmington, Michael Wilson, Mrs. Parkes, Mrs. Wilson's Sister, Peter Thomas, Tristan BalliolArabella Hammond, Charlotte Knight, DCI Tom Barnaby, DS Ben Jones, Ernest Balliol, Ned Fitzroy, Nesta Goodfellow, Patrick Bradley, Stanley Goodfellow
127Alistair Kingslake, Anthony Prideaux, Christa Palfrey, Daniel Snape, David Roper, Dr. Sylvia Goring, Ed Monkberry, Emily Harte, Felicity Law, Geoffrey Larkin, Graham Spate, Harriet Compton, Jim Hanley, Laurence Mann, Nicky Frazer, Richard Tanner--Grandfather Crisp, Gwendoline Frazer, Harry Godbolt, Mr. Crisp, Mr. FountainElizabeth Chettham, George Jeffers, Malcolm Frazer, Miles Tully
138Clifford Bunting, Faye Lennox, Fergal Jenner, Frank Bishop, Geoff Rogers, Gerald Ebbs, Giles Braithwaite, Hugh Dalgleish, Iris Holman, Jack Fincher, Jeff Bowmaker, Jenny Russell, Kitty Pottinger, Leo Fincher, Luke Archbold, Maureen Stubbs, Orlando Guest, Reverend Archie Moreland, Reverend Connor Gregory, Sonia Woodley, The vicar at Badger's DriftAn unnamed gardener, Landlord's daughter, Landlord's wifeAn unnamed landlord, Caroline Maria Roberts, Molly FieldingCaleb Burbage, David Langham, Douglas Stubbs, Gerald Woodley, Magnus, Mr. Barnaby, Mr. Conway, Mrs. Peach, Noah Fincher, Sebastian's Biological Father, Sir Richard Guillaume, The patients from Saint Fidelis HospitalDCI Tom Barnaby, DS Ben Jones, Dawn Stock, Giles Shawcross, Jude Langham, Katie Soper, Kenny Pottinger, Norman Swanscombe, Susan Fincher, Terry Stock
148Alex Preston, Conrad Walker, Dave Doggy Day, Dianne Price, Dr. Markham, Father Behan, Fran Carter, Freddie Raft, Gerry Dawkins, Len Merryman, Leticia Clifford, Mary Bingham, Mother Thomas, Olive Merryman, Patrick Morgan, Peter Slim, Sgt. Trevor Gibson, Thomas BrightwellCarolyn Lambert, Duncan Palmer, Jeremy Lambert, Max Fuller-Bertie Morell, Dr. Jonathan Frost, Mr. Vertue, Robin Bingham, Sir Anthony VertueByron Street, Claire Powell, DCI John Barnaby, DS Ben Jones, Ezra Canning, Jo Starling, Mother Julian, Nina Morgan, Paddy Powell, Silas Trout, William Bingham
156Caroline Garrett, Colin Yule, Cy Davenport, David Farmer, Debbie Moffett, Edward Stannington, Eve Lomax, George Dormer, Giles Harrison, Gregory Brantner, Helen Caxton, Jeremy Harper, Julian DeQuetteville, Keith Mulory, Kyle Gideon, Ludo DeQuetteville, Oliver Ordish, Peter GrovesDaniel Denning, Geoffrey DeQuetteville, Mrs. MuloryJason Winters, Phil CaxtonAngela DeQuetteville, Bentham DeQuetteville, Felicity Mary, George William Tilman, Jack Barton, Jim Caxton, Lady Elizabeth Rodney, Marion Green, Mary Dutta, Michael Harris, Miss Felton, Reverend Stannington, Ross, Rupert DeQuetteville, Tom Stanton, Vladimir KostelovBeatrix Ordish, DCI John Barnaby, DS Ben Jones, Diana Davenport, Finn Robson, Gagan Dutta, Grady Felton, Keith Mulory, Sasha Fleetwood, Sylvia Mountford, Ted Denning
165Amy Strickland, Atticus Bradley, Bernard King, Conor Bridgeman, Eddie Rayner, Eric Calder, Ernest Bradley, Frank Dewar, Julian Calder, Martin Strickland, Nancy Dewar, Philip Hamilton, Ross Clymer-Ellie WingateAgnes Trout, Henry Darnley, Joan Rayner, Little Rose Wilton, Max Thornfield, Mr. Fergus-Johnson, Tina BellArmand Stone, Gavin Hopkirk, Johnny Linklater, Nick's Stepfather, Ollie Tabori, Ruth Cameron
174Andrew Maplin, Annabel Latimer, Brian Grey, Cecilie Petersen, Frank Wainwright, George Summersbee, Hannah Altman, Louis Paynton, Nadia Simons, Niall Colebrooke, Suzie Colebrooke, Toby WinningJessica Tyler, Johnny Carver, Sir Hugo Melmoth-Enrico Latimer, Mark Sampson, Mr. Stowe, William HoltClaire Asher, DCI John Barnaby, DS Charlie Nelson, Luke Altman, Tina Tyler
186Aiden McCordell, Alex Dyer, Brandon Monkford, Carter Faulkner, Christopher Corby, Clara Myerscough, Dale Nevins, Felicity Ford, Greg Eddon, Harry Wyham, Lance Auden, Tony Pitt, Yasmin Ali, Zoe DyerEric TonevRichard MelroseGregory Lancaster, Lydia DryffieldDaniel Fargo, Des McCordell, Felix Lancaster
196Angus Colton, Azeem Meer, Cleo Langton, Finn Thornberry, Fitz Theara, Jacob Wheeler, Leo Henderson, Milo Craven, Roderick Craven, Ronin Chow, Samantha Berry, Seb Huntington, Vernon De HarthogFrank Lockston, The real Jacob Wheeler--Barbara Walton, DI Ben Jones, Errol Judd, Julian Lennard, Natalie Wheeler
206Adam Osoba, Aisha Khalique, Ashley Denton, Barrett Lounds, Dominic Braun, Emani Taylor, Francesca Lounds, Grady Palmerston, Laurel Newman, Mahesh Sidana, Mark Adler, Serena Madison, Terry BelliniBrother Jozef, Conrad ArgoNeville GallagherCurtis Ferabbee, Jane Scott, Jenny MossDS Jamie Winter, Sarah Barnaby, Stella Starling
214Alexander Beauvoisin, Cal Ingalls, Cornelius Tetbury, Dr. Serena Lowe, Duncan Corrigan, Eric Skye, Jemima Starling, Lex Bedford, Rosa CorriganNico Dearden-Lara Wokoma, Lola SilvermaneAmbrose Deddington, Artie Blythe, Blaise McQuinn, Cal Ingalls, Griffon Twigg
-126Adam Keyne, Adam Osoba, Agnes Gray, Aiden McCordell, Aisha Khalique, Alan Hollingsworth, Alan King, Alec Grainger, Alex Deakin, Alex Dyer, Alex Preston, Alexander Beauvoisin, Alistair Kingslake, Aloysius Wilmington, Amy Strickland, Andrew Maplin, Angus Colton, Anna Santarosa, Annabel Latimer, Anthony Prideaux, Archie Bellingham, Arthur Leggott, Ashley Denton, Atticus Bradley, Azeem Meer, Barrett Lounds, Bella Devere, Ben Gurdie, Bernard King, Brandon Monkford, Brenda Buckley, Brian Grey, Bruce Hartley, Cal Ingalls, Carla Constanza, Caroline Cave, Caroline Garrett, Carter Faulkner, Cathy Hewlett, Cecilie Petersen, Celia Armstrong, Charles Edmonton, Charles Jennings, Christa Palfrey, Christopher Corby, Clara Myerscough, Cleo Langton, Clifford Bunting, Colin Thomas, Colin Yule, Connor Simpson, Conor Bridgeman, Conrad Walker, Cornelius Tetbury, Cy Davenport, Dale Nevins, Daniel Snape, Daniel Talbot, Danny Pinchel, Dave Cutler, Dave Doggy Day, David Farmer, David Heartley-Reade, David Roper, David Whitely, Debbie Moffett, Debbie Shortlands, Dennis Rainbird, Dexter Lockwood, Dianne Price, Dominic Braun, Dr. Alan Delaney, Dr. Charles Rust, Dr. Duncan Goff, Dr. Gregory Ransom, Dr. Ian Aycott, Dr. James Lavery, Dr. John Osgood, Dr. Markham, Dr. Serena Lowe, Dr. Sylvia Goring, Dr. Wellow, Dudley Carew, Duncan Corrigan, Ed Monkberry, Eddie Darwin, Eddie Marston, Eddie Rayner, Edward Stannington, Eileen Carnack, Eleanor Crouch, Elizabeth Key, Elspeth Inkpen-Thomas, Emani Taylor, Emily Harte, Emily Simpson, Emma Tysoe, Eric Calder, Eric Skye, Ernest Bradley, Esslyn Carmichael, Eve Lomax, Father Behan, Faye Lennox, Felicity Ford, Felicity Inkpen-Thomas, Felicity Law, Fergal Jenner, Finn Thornberry, Fiona Thompson, Fitz Theara, Fran Carter, Francesca Lounds, Frank Bishop, Frank Dewar, Frank Hopkirk, Frank Wainwright, Frank Webster, Freddie Raft, Frederick Tomlin, Gareth Heldman, Geoff Rogers, Geoffrey Larkin, George Dormer, George Hamilton, George Summersbee, Gerald Bennett, Gerald Ebbs, Gerald Hadleigh, Gerry Dawkins, Giles Braithwaite, Giles Harrison, Ginny Sharp, Gordon Leesmith, Grady Palmerston, Graham Spate, Greg Eddon, Greg Tutt, Gregory Brantner, Gregory Chambers, Guy Gamelin, Guy Sandys, Guy Sweetman, Gwen Morrison, Hannah Altman, Harriet Compton, Harriet Davis, Harry Wyham, Harvey Crane, Hector Bridges, Helen Caxton, Henry Hammond, Henry Plummer, Henry York, Hugh Dalgleish, Hugo Cartwright, Ian Craigie, Ian King, Iris Holman, Iris Rainbird, Isobel Hewitt, Jack Colby, Jack Fincher, Jack Purdy, Jack Wilmot, Jacob Stokes, Jacob Wheeler, James Harrington, James Parkes, James Tate, Jamie Cruickshank, Jean Wildacre, Jeff Bowmaker, Jemima Starling, Jennifer Carter, Jenny Russell, Jeremy Harper, Jezebel Tripp, Jim Hale, Jim Hanley, Joanna Craxton, John Ransom, John Smith, John Whittle, Johnny Hammond, Julian Calder, Julian DeQuetteville, Julian Shepherd, Katherine Lacey, Keith Mulory, Keith Scholey, Kenneth Gooders, Kitty Pottinger, Kyle Gideon, Lady Lavinia Chetwood, Lady Sandra Butler, Lance Auden, Larry Smith, Laura Sharp, Laurel Newman, Laurence Mann, Len Merryman, Leo Fincher, Leo Henderson, Leonard Pike, Leticia Clifford, Lex Bedford, Lillian Webster, Lionel Bell, Liz Francis, Lloyd Kirby, Louis Paynton, Louise Purdy, Ludo DeQuetteville, Luke Archbold, Lydia Villiers, Lynton Pargeter, Madeline Villiers, Mahesh Sidana, Marcia Tranter, Marcus Lowrie, Marcus Steadman, Marina Fellowes, Marion Slade, Marjorie Empson, Mark Adler, Mark Castle, Martin Barrett, Martin Strickland, Martin Wroath, Mary Bingham, Mary Mohan, Maureen Stubbs, Max Jennings, Max Ransom, Melissa Townsend, Michael Darrow, Mike Spicer, Mildred Danvers, Miles King, Milo Craven, Mimi Clifton, Molly Thomas, Mother Thomas, Munro Hilliard, Nadia Simons, Nancy Dewar, Neville Williams, Niall Colebrooke, Nick Cheyney, Nicky Frazer, Nicky Harding, Olive Merryman, Oliver Ordish, Orlando Guest, Otto Benham, Owen August, Owen Swinscoe, Patrick Morgan, Patrick Pennyman, Peggy Benson, Peter Cave, Peter Drinkwater, Peter Groves, Peter Slim, Philip Hamilton, Raif Canning, Rev. Anthony Gant, Reverend Archie Moreland, Reverend Connor Gregory, Rex Masters, Richard Bayly, Richard Rackham, Richard Tanner, Robin Lawson, Robin Wooliscroft, Roderick Craven, Ron Pringle, Ron Wilson, Ronin Chow, Rosa Corrigan, Rosemary Furman, Ross Clymer, Ruth Fairfax, Sam Judd, Samantha Berry, Samantha Johnstone, Seb Huntington, Serena Madison, Sgt. Trevor Gibson, Simon Bright, Simon Fletcher, Simon Mayfield, Simon Reason, Sonia Woodley, Stephen Bannerman, Steve Bright, Steve Ramsey, Steven Curtis, Susan Bartlett, Suzanna Chambers, Suzie Colebrooke, Tara Cavendish, Terrance Lowther, Terry Bellini, The vicar at Badger's Drift, Thomas Brightwell, Toby Winning, Tony Kirby, Tony Parish, Tony Pitt, Trevor Machin, Tristan Goodfellow, Vernon De Harthog, Victoria Bartlett, Yasmin Ali, Zoe DyerAn unnamed gardener, Andy Moore, Bella Trace, Brother Jozef, Carolyn Lambert, Catherine Hamilton, Conrad Argo, Cynthia Bennett, Daniel Denning, Duncan Palmer, Emily Beavis, Eric Edwards, Eric Tonev, Eva Hoffman, Frances Trevelyan, Frank Lockston, Gaius Quintus, Geoffrey DeQuetteville, Jeremy Lambert, Jessica Tyler, Joan Chaplin, Johnny Carver, Jonathan Lowrie, Judith Albiston, Karl Wainwright, Landlord's daughter, Landlord's wife, Maria Godbold, Max Fuller, Mr. Hanlon, Mrs. Foster, Mrs. Mulory, Nico Dearden, P.C. Colin Armstrong, Reverend Jonathan Ebbrell, Roger Heldman, Simon Tranter, Sir Hugo Melmoth, The real Jacob Wheeler, Tommy HicksAn unnamed landlord, Caroline Maria Roberts, Claire English, David Key, Ellie Wingate, Evelyn Pope, Ferdy Villiers, Gilly Galsworthy, Gina Colby, Ginger Foxton, Grahame Tranter, Jason Winters, Michael Lacey, Molly Fielding, Mr. Purdy, Neville Gallagher, Peter Baxter, Phil Caxton, Phyllis Cadell, Rev. Stephen Wentworth, Richard Melrose, Rosemary Key, Sarah Kirkwood, St John Smythe-Webster, Stanley Lockwood, Tammie, the mother of John Field, Vivienne MarwoodAgnes Trout, Angela DeQuetteville, Bella Slade, Bentham DeQuetteville, Bertie Morell, Caleb Burbage, Charlie Finleyson, Colonel Halsey, Colonel Hartley, Conor Maplin, Curtis Ferabbee, Cyrus LeVanu, Dave Ripert, David Langham, David MacKillop, Douglas Stubbs, Dr. Jonathan Frost, Duggie Hammond, Eddie Field, Ellis Bell, Elsie Pinchel, Enrico Latimer, Felicity Mary, Felix Bryce, George William Tilman, Gerald Empson, Gerald Woodley, Giles Southerly, Grandfather Crisp, Gregory Lancaster, Gwendoline Frazer, Harry Godbolt, Henry Darnley, Honoria Lyddiard, Isabel Aubrey, Jack Barton, Jane Scott, Jean Waverley, Jennifer Bryce, Jenny Moss, Jim Caxton, Joan Alder, Joan Rayner, John Lampson, Kenneth Hewitt, Lady Elizabeth Rodney, Lady Fitzroy, Lara Wokoma, Lettie Edwards, Libby Wilson, Little Rose Wilton, Lola Silvermane, Lord Fitzroy, Lucinda Thacker, Lucy Wilmington, Lydia Dryffield, Madge Fielding, Magnus, Marion Green, Mark Sampson, Mary Dutta, Matthew Draper, Maurice Plummer, Max Thornfield, Michael Bannerman, Michael Harris, Michael Weston, Michael Wilson, Miss Felton, Mr. Barnaby, Mr. Barrett, Mr. Conway, Mr. Crisp, Mr. Fergus-Johnson, Mr. Fountain, Mr. Lacey, Mr. Monday, Mr. Stowe, Mr. Vertue, Mrs. Barrett, Mrs. Cole, Mrs. Lacey, Mrs. Mortimer, Mrs. Parkes, Mrs. Peach, Mrs. Scholey, Mrs. Wilson's Sister, Muriel Harrap, Neil Laxton, Noah Fincher, Patricia Smith, Peter Thomas, Ralph Lyddiard, Ralph Wood, Reggie Barton, Reverend Stannington, Robin Bingham, Ronald Stokes, Ross, Rupert DeQuetteville, Ruth Weston, Sebastian's Biological Father, Simone's mother, Sir Anthony Vertue, Sir Freddy Butler, Sir Jonathan Haslett, Sir Richard Guillaume, Sir. Walter Talbot, Susan Bannerman, Ted Hayward, The patients from Saint Fidelis Hospital, Tina Bell, Tom Stanton, Tristan Balliol, Vladimir Kostelov, William Carter, William HoltAgnes Waterhouse, Alan Clifford, Alistair Gooding, Ambrose Deddington, Amy Lyddiard, Antonia Wilmot, Arabella Hammond, Armand Stone, Artie Blythe, Augustus Deverell, Barbara Walton, Beatrix Ordish, Blaise McQuinn, Byron Street, Cal Ingalls, Camilla Crofton, Carol Prentice, Carolyn Armitage, Charles King, Charlotte Knight, Christian Aubrey, Claire Asher, Claire Powell, Clarice Opperman, Colin Hawksley, Cully Barnaby, DCI John Barnaby, DCI Tom Barnaby, DI Ben Jones, DS Ben Jones, DS Charlie Nelson, DS Dan Scott, DS Gavin Troy, DS Jamie Winter, Daniel Fargo, David Merrill, Dawn Stock, Des McCordell, Diana Davenport, Doris Winstanley, Dr. James Kirkwood, Dr. John Cole, Elizabeth Chettham, Ernest Balliol, Errol Judd, Evelyn Pope, Ezra Canning, Felix Lancaster, Finn Robson, Frances Le Bon, Gagan Dutta, Gavin Hopkirk, George Jeffers, Giles Shawcross, Grady Felton, Griffon Twigg, Hector Bridges, Ian King, Jack Magwood, Jo Starling, John Merrill, Johnny Linklater, Jonah Bloxham, Jude Langham, Julia Gooders, Julian Lennard, Katie Soper, Keith Mulory, Kenny Pottinger, Liam Booker, Little Mal Kirby, Luke Altman, Lydia Villiers, Malcolm Frazer, Mallory Edmonton, Margaret Winstanley, Martin Spellman, Michael Bannerman, Miles Tully, Miriam Heartley-Reade, Mother Julian, Mr. Miller, Mrs. Beverley, Mrs. Partridge, Naomi Inkpen, Natalie Wheeler, Ned Fitzroy, Nesta Goodfellow, Neville Hayward, Nick's Stepfather, Nina Morgan, Norman Swanscombe, Ollie Tabori, Paddy Powell, Patrick Bradley, Peter Craxton, Peter Fogden, Phil Harrison, Robert Cavendish, Ruth Cameron, Sam Callaghan, Samantha Johnstone, Sandra Tate, Sarah Barnaby, Sasha Fleetwood, Sean O'Connell, Selwyn Proctor, Silas Trout, Sir John Waverley, Sir. Anthony Talbot, Stanley Goodfellow, Stella Starling, Susan Fincher, Sylvia Mountford, Ted Denning, Terry Stock, Tim Riley, Tina Tyler, WPC Jay Nash, William Bingham

Last line has NULL in column season and is the grand total.

Each list of names is ordered alphabetically and delimited by comma.
Each list is distinct, so that in for example persons_attempt in the grand total DS Tom Barnaby does not appear multiple times.

12 comments:

  1. Testing the commentary...

    This is the link to the script with the table and data setup:
    https://livesql.oracle.com/apex/livesql/s/j7jv2j88r9sishxgns1876dhn

    ReplyDelete
  2. https://livesql.oracle.com/apex/livesql/s/j7jw18g5kxifbu4k9mam6a1u8

    ReplyDelete
    Replies
    1. First entry, and already I am learning new stuff :-D

      Delete
  3. OK, with cross-site tracking allowed I can publish, but preview just makes the content disappear.

    If I happen to win, sign away!

    ReplyDelete
    Replies
    1. Thanks for your entry, Stew. I look forward to seeing the different solutions people can come up with ;-)

      Sorry for the comment posting issues. I can see this has been an issue at least a year without any fix from Google :-(

      Delete
  4. https://livesql.oracle.com/apex/livesql/s/j7scovpxwt5snzc6se8fhj23i

    ReplyDelete
    Replies
    1. Ooooh - interesting mix of techniques. This is turning into a learning experience 😁

      Delete
  5. I've added two additional solutions (inspired by Stew's solution, I like it very much, even if listagg has its limits).

    One using XMLTYPE and SQL/Query without additional UDT. However, I could not make it work without some helper PL/SQL functions using SQL. So, from a performance point of view I do not like it.

    https://livesql.oracle.com/apex/livesql/s/j79o7hf3603k1ti8dy4s5q7ax

    The other solution does the heavy lifting using XQuery. No nested SQL calls anymore. Much better from a performance point of view.

    https://livesql.oracle.com/apex/livesql/s/j79nycyvrzu6hgjz4xev6bcsp

    Cheers.

    ReplyDelete
  6. "listagg has its limits". Yes, I decided to use it because the rule stated "The SQL statements may use anything that is available in a standard LiveSQL session" - including Extended Data Types.

    You do know that fn:tokenize is documented as "not supported" - though I have no idea why, I have never seen it not work.

    Anyway, nice solution leveraging Xquery more fully!

    Cheers, Stew

    ReplyDelete
    Replies
    1. Thank you, Stew, for your feedback.

      I use a default max_string_size on my local machine, so I run into errors when using listagg. At that time I did not know that LiveSQL is using extended data types, so I haven’t thought about changing it in my configuration and changed the approach without thinking too much about it. It’s just sad that listagg does not work with CLOB and we have to switch to a DIY approach if we exceed the limits and do not want to cut off some content at the end. I looked at your solution after completing my first one and was surprised that you are using listagg. It obviously worked. That’s when I learned that LiveSQL has set to max_string_size to extended. As you said, using listagg in this case is perfectly valid. I dislike the size limitation of listagg in general and not in your case.

      What I really liked in your solution is the intermediate data set. The idea to distribute the array of names accross rows is very nice. And the implementation is elegant. This idea alone should make you the winner of this competition. :-)

      Regarding “fn:tokenize”. No, I was not aware that it is not supported. In fact I never had a problem in the past either. I had a look at the documentation. https://docs.oracle.com/en/database/oracle/oracle-database/19/adxdb/xquery-and-XML-DB.html#GUID-22940B8D-1468-4966-9F55-7DD4518E9612 . Strange that it is working. Maybe it’s a documentation bug. The reason why I seldom use “ora:” functions is that I develop the XQueries using Stylus Studio XML and use the common XQuery/XPath documentation. ora: functions would not work there anyway. Another disadvantage of this approach is, that I sometimes have to rewrite things, because the supported feature set in the Database is based on older versions (happend for the XQuery 3.0 feature “group by” in this case). However, I like the advantages of a XML/XPath/XQuery IDE and therefore stick to this approach. In any case, thank you for the hint.

      Cheers, Philipp

      Delete
  7. Hi Kim,
    I am more a DBA than a developer, but nevertheless I took the challenge.
    Here is my solution:
    https://livesql.oracle.com/apex/livesql/s/j8symtvpt63ina8b5v2j7mw26
    Cheers Mathias

    ReplyDelete
    Replies
    1. Thanks for the entry, Mathias
      Great to see a completely different technique than the previous entries ;-)

      And sure this is for everybody who uses SQL. I don't judge whether DBA or developer - we all work with data :-D

      Delete