CODEWITHSUNDEEP
sqlgoogle-bigquery
user9203730
user9203730

Reputation:

Select all column except one column in bigquery

I want to exclude count to remove duplicates in below query 

SELECT *, count(1) as count except (count)
  FROM PRD.sites  
  group by site,id ,site_desc,timestamp
  having count(1) < 2

but got error Unexpected keyword EXCEPT at [1:29]. I am using standardSQL

Upvotes: 2

Views: 13596

Answers (3)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172954

You don't even need to include COUNT() into SELECT list - below will perfectly work and will remove duplicate rows based on fields enlisted 

#standardSQL
SELECT AS VALUE ANY_VALUE(t)
FROM PRD.sites t 
group by site,id ,site_desc,timestamp
having count(1) < 2

Above will show only rows which do not have dups (based on fields in group by)

If you rather need to remove dups based on fields - use below 

#standardSQL
SELECT AS VALUE ANY_VALUE(t)
FROM PRD.sites t 
group by site,id ,site_desc,timestamp

If you need to de-dup based on whole row - you can use below 

SELECT DISTINCT *
FROM PRD.sites

Upvotes: 1

Priyank_Vadi
Priyank_Vadi

Reputation: 1138

You can try with temp table

/* Get the data into a temp table */
SELECT * INTO #TempTable
FROM YourTable
/* Drop the columns that are not needed */
ALTER TABLE #TempTable
DROP COLUMN ColumnToDrop
/* Get results and drop temp table */
SELECT * FROM #TempTable
DROP TABLE #TempTable

Upvotes: 1

Fahmi
Fahmi

Reputation: 37473

You can try below

select * except(count)
from
(
  SELECT *, count(1) as count 
  FROM PRD.sites  
  group by site,id ,site_desc,timestamp
  having count(1) < 2
)X

OR You can use row_number() for finding duplicates

SELECT * except(rn)
FROM (
  SELECT
      *,
      ROW_NUMBER()
          OVER (PARTITION BY site,id ,site_desc,timestamp) as rn
  FROM FROM PRD.sites
)
WHERE rn= 1

Upvotes: 6

Related Questions