Use Queries to show records based on conditions

Follow

Comments

16 comments

  • Avatar
    Gustavo A. Valero P.

    Hi,

    I have a little problem using LABEL and ORDER clauses at the same time.

    If I write:

    SELECT A, C, D ORDER BY D       -->   the query works fine

    but with:

    SELECT A, C, D LABEL A ' Name', C 'Phone' ORDER BY D   -->   the query sends an error message!!

    Is there a little bug or I'm missing anything? 

    Thanks in advance for your help!

    Gavp

    BTW...  great web site, product and ideas, congratulations!!

    0
    Comment actions Permalink
  • Avatar
    Awesome Table

    Hello Gustavoavalerop,

    Actually, when you use SELECT and ORDER, ORDER must be just after SELECT. I suggest you use:

    SELECT A,C,D ORDER BY D LABEL A 'Name', C 'Phone'

    It should work with this method.

    Kind regards.

    Thomas

    0
    Comment actions Permalink
  • Avatar
    Sean Gillard

    Great Article! it helped me on some of the things I have been working on.

    I have one that I'm asking it to essentially filter more. But once I get to a certain point, Then the rest stops working. What am I doing wrong?  In the below formula, A is bringing me Active and inactive. Manager sometimes shows up and sometimes doesn't depending on if I add the Active one or not. Is there a specific way this order should be to make it work?

    =QUERY(Lookup!A:Y, "select B, E, F, O, P, Q, W, X where B = 'Operations' and W = 'Supervisor' or W= 'Lead' or W = 'Manager' and A = 'Active' ")

     

     

    Sean

    0
    Comment actions Permalink
  • Avatar
    Thomas Morin

    Hello Sean Gillard,

    Actually, your syntax is not correct. You don't need to use"=QUERY(Lookup!A:Y...".

    Your query must be like this:

    select B, E, F, O, P, Q, W, X where B = 'Operations' and W = 'Supervisor' or W= 'Lead' or W = 'Manager' and A = 'Active' 

    In this way, it will work because that's the correct syntax.

    Thank you.

    Thomas

     

    0
    Comment actions Permalink
  • Avatar
    Dave HUDDART

    Hello

    Is it possible to SELECT rows that are not duplicates (based on value in a specified column)?
    I can deduplicate in Google Sheets, but was wondering if I could do it in an Awesome Table view.

    Best regards

    Dave

    0
    Comment actions Permalink
  • Avatar
    Thomas Morin

    Hello Dave HUDDART,

    It is definitely possible to select rows depending on specific term or criteria. You should consult this part of this article to know how to achieve that.

    Kind regards.

    Thomas

    0
    Comment actions Permalink
  • Avatar
    Brandon Carraway

    🙌🙌

    The SORT option was VERY helpful; I needed newest data at the top, and doing this through Google Sheets is tedious. This saved me SO much headache!!! ❤❤

    0
    Comment actions Permalink
  • Avatar
    Thomas Morin

    Hello Brandon Carraway,

    Thank you for your message! 

    We are really glad you like our product and you find it is useful.

    Kind regards.

    Thomas

    0
    Comment actions Permalink
  • Avatar
    Luís Esteves

    Hi,

    Is it possible to add a button to sort the results in diferent ways ? I'd like to have the possibility to sort by one colums (alphebeticaly) or a diferent way by clicking on a button.

     

    Thanks !

    0
    Comment actions Permalink
  • Avatar
    Nicolas Gauvin

    Hello Luis Esteves,

    There are no ways to change the sorting or query with a button.

    Nicolas

    0
    Comment actions Permalink
  • Avatar
    Brandon Carraway

    How to sort two columns (L, I)

     

    I need to sort by column L desc, then by I desc I tried this: "select * order by L desc and order by I desc" even using a "," instead of "and" as I was reading through the above guide, but I can't seem to figure it out. 🤦‍♂️

    0
    Comment actions Permalink
  • Avatar
    Randolph Abelardo (Edited )

    Hi @Brandon.

    Instead of select * order by L desc and order by I desc, use this statement:

    select * order by L desc, I desc

    Please reference Task # 4 above for more info.

    Hope this helps. 

    - Randy

    0
    Comment actions Permalink
  • Avatar
    Florentin von Haugwitz

    Hello, is it possible to use a group by statement?
    Thank you

    0
    Comment actions Permalink
  • Avatar
    Randolph Abelardo (Edited )

    Hi @Florentin.

    The group by clause can and will work when entered in the Awesome Table editor's Query field.
    But I don't recommend using this.
    Please note that since the group by clause can only be used with an aggregation function, it will perform the specified aggregation (avg, count, max, min, sum) and in effect:

    • Bypass your original datasource (negating the parameters [2nd] row containing filters and keywords)
    • And will use the aggregated output you specified, instead (only the columns you specified in the Query field will be displayed
    • This will also render your template useless. 

    An alternative would be to use the order by clause to cluster items based on a specified category. More about this on Tasks # 3 - 4 above.

    Hope this helps.


    - Randy

    0
    Comment actions Permalink
  • Avatar
    Jen Giffen

    I have built an awesome table that has topic (COL A), grade (COL B), and curriculum standards met in different subject areas (COL C-K). 

    I filtered as follow:

    COL A CategoryFilter(B) - Hidden

    COL B CategoryFilter

    The rest of the columns have no filter

    I want the results to only show columns with data in them.

    Right now a search might yield this:

    but I want all the columns with blank entries (in this case COL C,D, F,G, H, etc) removed.  Is there a query for this?

    Jen

    0
    Comment actions Permalink
  • Avatar
    Randolph Abelardo (Edited )

    Hi @Jen.

    To avoid displaying blank cells (and their corresponding rows) on your view, simply follow the instructions detailed in Task # 13 above. Since you need to specify multiple non-blank columns, you need to use the logical AND operator. For example, the Query statement below will specify that it will only show or display rows where (all 3) columns A, B and F doesn't have blank entries. 

    select * where A != '' and B != '' and F != ''

    > Just replace and/or add the relevant columns in this statement for your use case.

    * Don't forget to click the UPDATE VIEW button for the Query statement to take effect.


    - Randy

     

     

    0
    Comment actions Permalink

Please sign in to leave a comment.