search

AlaSQL / alasql

AlaSQL.js - JavaScript SQL database for browser and Node.js. Handles both traditional relational tables and nested JSON data (NoSQL). Export, store, and import data from localStorage, IndexedDB, or Excel.
http://alasql.org
MIT License
7.01k stars 653 forks source link

SELECT with GROUP BY #586

Closed MarioVanDenEijnde closed 8 years ago

MarioVanDenEijnde commented 8 years ago

Hello ALASQL team,

It seems I have to add every SELECT column (res1) also to the GROUP BY Like below if I don't add "Aannemer" to the GROUP BY it will not be part of the result. But I have many columns in res1. Is this the only way?

res2 = alasql('SELECT Project, Aannemer, ARRAY(b._) AS keuringen FROM ? a JOIN ? b USING Project GROUP BY Project,Aannemer', [res1,res]);

Kind regards, Mario

agershun commented 8 years ago

You can use FIRST() aggregator like:

res2 = alasql('SELECT Project, FIRST(Aannemer) AS Aannemer, ARRAY(b._) AS keuringen \
     FROM ? a JOIN ? b USING Project GROUP BY Project', [res1,res]);
MarioVanDenEijnde commented 8 years ago

Thanks. That did it. There is not something that includes all available columns?

agershun commented 8 years ago

Unfortunately, currently no (because, there is not such functionality in original SQL).

@mathiasrw What do you think about FIRST(*) extension of SQL to save all columns from the record to prevent multiple columns in GROUP BY?

SELECT FIRST(*) FROM a GROUP BY id
SELECT FIRST(a.*) FROM a JOIN b USING id GROUP BY id
mathiasrw commented 8 years ago

FIRST(*) would be a very neat addition to the specs :+1: