MAX/MIN function not working for words #411

Activity

1. trittberg commented on Sep 12, 2015

   

   trittberg

   on Sep 12, 2015

   Author

   More actions

   Sorry, by data example was wrong ... more like

   [ {field1: value1, field2: value2} ],
   [ {field1: valueA, field2: valueB} ]
   

   Sorry,
   Timm
2. mathiasrw commented on Sep 14, 2015

   

   mathiasrw

   on Sep 14, 2015

   Member

   More actions

   Hey @trittberg - please provide your SQL so we can help you debug...
3. trittberg commented on Sep 16, 2015

   

   trittberg

   on Sep 16, 2015

   Author

   More actions

   Hi Mathias

   Here is my dilemma.

   SQL: "SELECT MAX(text) AS text FROM ?"
   DATA: [{"text":"ConfigPanel","value":"ConfigPanel"},{"text":"ConfigPanelEdit","value":"ConfigPanelEdit"},
   ... ]
   RESULT: alasql(SQL, [data])[0].text = undefined

   Please see the attachment for SQL, data and result details.

   Thanks,
   Timm

   On 14 September 2015 at 02:47, Mathias Rangel Wulff <
   notifications@github.com> wrote:

   Hey @trittberg https://github.com/trittberg - please provide your SQL
   so we can help you debug...

   —
   Reply to this email directly or view it on GitHub
   #411 (comment).

   Cheers,
   Timm
4. mathiasrw commented on Sep 17, 2015

   

   mathiasrw

   on Sep 17, 2015

   Member

   More actions

   The implementation does not let minand max handle other values than numbers

   sql = "SELECT max(text) as text FROM ?"
   data = [{"text":"2","value":"ConfigPanel"},{"text":"5","value":"ConfigPanelEdit"}]
   console.log(JSON.stringify(alasql(sql, [data])))
   // [{"text":5}]
   
   sql = "SELECT max(text) as text FROM ?"
   data = [{"text":"abc","value":"ConfigPanel"},{"text":"def","value":"ConfigPanelEdit"}]
   console.log(JSON.stringify(alasql(sql, [data])))
   // [{}]
   

   Im not sure how other databases handles minand max - but we will have to look into it.

   I recommend you order and limit to one to get the same result with text

   sql = "SELECT text as text FROM ? ORDER BY text DESC LIMIT 1"
   data = [{"text":"abc","value":"ConfigPanel"},{"text":"def","value":"ConfigPanelEdit"}]
   console.log(JSON.stringify(alasql(sql, [data])))
   // [{"text":"def"}]
   
5. mathiasrw

   added

   ! For discussion

   on Sep 17, 2015
6. mathiasrw

   changed the title [-]MAX/MIN function not working for JSON data[/-] [+]MAX/MIN function not working for words[/+] on Sep 17, 2015
7. trittberg commented on Sep 17, 2015

   

   trittberg

   on Sep 17, 2015

   Author

   More actions

   Ah, that's the issue. I am also an ms sql server guy, so i didn't know
   about the limit command. I'm used to select top. I will try your
   solution. Thank you.

   Timm

   On Thu, Sep 17, 2015, 7:12 AM Mathias Rangel Wulff notifications@github.com
   wrote:

   The implementation does not let minand max handle other values than
   numbers

   sql = "SELECT max(text) as text FROM ?"
   data = [{"text":"2","value":"ConfigPanel"},{"text":"5","value":"ConfigPanelEdit"}]
   console.log(JSON.stringify(alasql(sql, [data])))
   // [{"text":5}]

   sql = "SELECT max(text) as text FROM ?"
   data = [{"text":"abc","value":"ConfigPanel"},{"text":"def","value":"ConfigPanelEdit"}]
   console.log(JSON.stringify(alasql(sql, [data])))
   // [{}]

   Im not sure how other databases handles minand max - but we will have to
   look into it.

   I recommend you order and limit to one to get the same result with text

   sql = "SELECT text as text FROM ? ORDER BY text DESC LIMIT 1"
   data = [{"text":"abc","value":"ConfigPanel"},{"text":"def","value":"ConfigPanelEdit"}]
   console.log(JSON.stringify(alasql(sql, [data])))
   // [{"text":"def"}]

   —
   Reply to this email directly or view it on GitHub
   #411 (comment).
8. mathiasrw commented on Sep 18, 2015

   

   mathiasrw

   on Sep 18, 2015

   Member

   More actions

   Good to know you could use the answer... AlaSQL also supports the MSSQL notation:

   sql = "SELECT TOP 1 text FROM ? ORDER BY text DESC"
   data = [{"text":"abc","value":"ConfigPanel"},{"text":"def","value":"ConfigPanelEdit"}]
   console.log(JSON.stringify(alasql(sql, [data])))
   // [{"text":"def"}]
   
9. mathiasrw

   closed this as completedin #1156on Jan 17, 2020
10. rahulraj-120799 commented on Aug 12, 2021

    

    rahulraj-120799

    on Aug 12, 2021

    More actions

    MONTHNAME(), GETDATE is not working in alasql