• Welcome, Guest. Please login.
 
September 22, 2019, 01:35:20 pm

News:

Welcome to the SQLitening support forums!


Lowest of each group example

Started by cj, March 06, 2015, 09:51:30 am

Previous topic - Next topic

cj

March 06, 2015, 09:51:30 am Last Edit: March 06, 2015, 10:07:41 am by cj
'slSelAry "select team, min(Age), Player from mytable group by Team",sArray(),"Q9"

#DIM ALL
#INCLUDE "sqlitening.inc"  'Youngest player on each team example
'
FUNCTION PBMAIN () AS LONG 'GroupBy2.bas
  LOCAL sArray() AS STRING
  slopen "sample.db3","C"                      'open or create sample.db3
  slexe "drop table if exists mytable"         'drop table if it already exists
  slexe "create table if not exists mytable(Player,Age,Team)" 'create table
  '
  'insert player#, age#, team#
  slexe "insert into mytable values (1,15,2)"  'player 1, age 15, team 2
  slexe "insert into mytable values (2,14,1)"  'player 2, age 14, team 1
  slexe "insert into mytable values (3,13,3)"  'player 3, age 13, youngest team 3
  slexe "insert into mytable values (4,12,1)"  'player 4, age 12, team 1
  slexe "insert into mytable values (5,11,1)"  'player 5, age 11, youngest team 1
  slexe "insert into mytable values (6,10,2)"  'player 6, age 10, youngest team 2
  '
  'Select by lowest age on each team into an array with each column  separated by CHR$(9)
  slSelAry "select team, min(Age), Player from mytable group by Team",sArray(),"Q9"
  '
  'Joins each row/elmement into a string delimited by a carriage return for displaying
  ? JOIN$(sArray(),$CR),,"Youngest player on each team"
'Results:
  'Youngest player on each team
  'Team  min(age)  Player
  '1     11        5
  '2     10        6
  '3     13        3
END FUNCTION