Bigquery: Select a column with any value not in the group by clause

It is a classical question and I know there are many work around like here: Select a Column in SQL not in Group By but they do not work for my issue on Bigquery.

I have a table with tweets from Twitter and I want a ranking for the urls including any tweet text.

ID         tweet               url
1          my github tweet     http://www.github.com/xyz
2          RT github tweet     http://www.github.com/xyz
3          another tweet       http://www.twitter.com
4          more tweeting       http://www.github.com/abc

I tried the following query, but then id 1 and 2 are counted separately.

SELECT tweet, count(url) as popularity, url FROM table group by tweet, url order by popularity desc

How can I count/rank the urls correctly and still preserve any associated tweet text in the result? I do not care if it is from ID 1 or 2.

Here is one approach:

SELECT url, COUNT(*) AS popularity, GROUP_CONCAT(tweet)
FROM Table GROUP BY url ORDER BY popularity

GROUP_CONCAT aggregation function will concatenate all the tweets associated with same URL using comma as separator (you can pick another separator as second parameter to GROUP_CONCAT).

MySQL error & ldquo; Expression # 1 of the ORDER BY clause is not in the GROUP BY clause & rdquo;

I am trying to fetch total number of records from a table and using the following MySQL query: SELECT COUNT(*) AS cnt FROM `info` WHERE 1 GROUP BY FROM_UNIXTIME(signup_date, '%Y-%m-%d') ORDER BY signup_date DESC but it is resulting in the following e

Select all columns with distinct values from a table

I have a table that contains these columns ID, NAME, JOB what I want is to select one record of every distinct job in the table from this table ID NAME JOB 1 Juan Janitor 2 Jun Waiter 3 Jani Janitor 4 Jeni Bartender to something like this ID NAME JOB

Compare two SQL Server columns with a value and insert the output in the third column

I have two columns WL-Value and RF-Value, if both have any value then we have insert value in third column "Final". If those any columns have 0 or null value then only need to take column heading text which column have value. I need query for in

Mysql column with null values - what are the space requirements?

I have a table with quite a lot entries. I need an additional column with an integer value or null. The thing is that only very few rows will have that field populated. So i wonder whether its better to create a seperate table where i link the entrie

SQL - query and replace the value of the column with another value extracted from the same table

I have a table with the following columns: ID | NAME | ID_PREVIOUS | ID_FOLLOWING | RANDOM_USELESS_DATA ----+--------+--------------+--------------+--------------------- 1 | AB2-F | NULL | 2 | bla bla bla 2 | PP-01 | 1 | 3 | ffwfwqafrwf 3 | 10J6U | 2

How to replace "strings" with numeric values based on a group by clause

I have the below table (#temp1) where I need to replace the string in the column'Formula' with the matching input 'VALUE' column based on the group 'Yearmonth'. The 'Formula' column may be of any mathematical expression for better understanding I hav

Select a field that is not in the Group By clause in LINQ

could anybody help me about "How to select a field that is not in Group By clause in LINQ" var ReportData = from a in context.Table1 from b in context.Table2 where a.personelID == b.ID && a.DateField.Value.Year == 2011 group a by new { a

DB2 can not select a column with null values

I have my request where co.DATE_UTILISATION is an integer can be null : SELECT COUNT(DISTINCT co.NUMERO) as TOTAL , co.DATE_UTILISATION, cast(NULL as int) as TEST FROM FOIATST.coupons as co LEFT OUTER JOIN FOIATST.operateurs as op ON co.OPERATEUR = o

Find the last cell in a column with a value and move the values to new columns / cells

I have a macro I'm working on for my job, and I'm stumped. In my Excel sheet, I have a column (column IE) which will have either "Monthly", "Bimonthly", or "Quarterly" in the cells in that column, depending on the account in

How can I add a column with specific values and calculate the percentage?

I have received from a select query this kind of table (just a simple example): D C Letter 20153 200 x 20154 300 x 20161 250 x 20162 180 x 20153 500 y 20154 380 y 20161 550 y 20162 170 y 20153 230 z 20154 700 z 20161 210 z 20162 185 z So, the column

Query for multiple columns with multiple values but in the different table with the same column

I know the title does not understand anything. But I would not know how to define it better. I will explain more my problem. I have 2 tables. A which in its interior has the following values: Name table: battle res1 res2 winner 225 552 552 In the sec

SQL Server GROUP BY that includes the column in SELECT but not in the GROUP BY clause

Please help me to solve this. I have this table note: Notes I want only this display: Highest note strength value, only 1 display if more than 1 same note strength value of distinct SubjectID and Stat The problem is there were notes that have the sam

postgres - the partial column in the SELECT / GROUP BY column - must appear in the GROUP BY clause or be used in an aggregate function

Both the following two statements produce an error in Postgres: SELECT substring(start_time,1,8) AS date, count(*) as total from cdrs group by date; SELECT substring(start_time,1,8) AS date, count(*) as total from cdrs group by substring(start_time,1

Postgres another problem with the column 'ldquo' should appear in the GROUP BY clause or be used in an aggregated function & rdquo;

I have 2 tables in my Postgres database. vehicles - veh_id PK - veh_number positions - position_id PK - vehicle_id FK - time - latitude - longitude .... few more fields I have multiple entries in Position table for every Vehicle. I would like to get

Related Articles