Question About fusiontable SQL


#1

Fusion table is like below.

fusiontable_01

I made query like this.

SELECT number, pin, auth FROM table_id WHERE name=‘t1’ AND amount=5000

So result is two rows; number=1, number=3.
But I want only one row.(Any row is okay…)
I used ‘SELECT DISTINCT’, but error occurred.
Is there any good solution?


#2

I haven’t used FusionTable yet but have you tried GROUP BY?


#3

SELECT number, pin, auth FROM table_id WHERE name=‘t1’ AND amount=5000 LIMIT 1
Taifun


Trying to push the limits! Snippets, Tutorials and Extensions from Pura Vida Apps by Taifun.


#4

@Taifun

Thank you for your reply. :slight_smile:
You mean…Should I insert ‘LIMIT 1’ in code using Taifun extension?


#5

@Ken_Nichols

Not yet.
I guess ‘GROUP BY’ is not ANSI SQL. Isn’t it?
I know appybuilder supports ANSI SQL.


#6

@Taifun’s solution looks like your best option.


#7

@Ken_Nichols

Thank you so much :slight_smile:
I tried it just. And I solved the problem.^^


#8

we are talking about the fusiontable SQL here and not SQlite
see the documentation of the fusiontable SQL language here https://developers.google.com/fusiontables/docs/v2/sql-reference#Select

LIMIT
Returns only a given number of rows. The purpose is to avoid getting more data than can be handled by the application or computer resources on the receiving end.

Taifun


#9

@Taifun

Thank you for your kindness :slight_smile:


#10

@Taifun

I have one more question.
If I get the max value of number field, what should I do?
At the table, the max value is 5, and I want to get it using SELECT Query.

Frankly speaking, I tried to insert name, amount, pin, auth except number using Insert block of fusion table component. I expected that number would be inserted automatically.


#11

see again https://developers.google.com/fusiontables/docs/v2/sql-reference#Select
and try SELECT MAXIMUM(number) FROM <tableid>

Taifun


#12

@Taifun

I mistook ‘MAXIMUM’ for ‘MAX’. So error had occurred continuously. :frowning:
Thank you soooo much :slight_smile: