CODEWITHSUNDEEP
google-bigquery
Ipkiss
Ipkiss

Reputation: 801

Order by column distinct

I have two columns say time(HH:MM:SS) format and message(String) what i wanted to do was to order using message distinct but since i have time column i still get duplicates ex

time         message
10:00:01     hi
10:00:02     hi

the expected query should return

time         message
10:00:01     hi

or 

time         message
10:00:02     hi

as long as the message is one of them since they are the same

i have tried but this didn't work. is this possible to achieve?

select distinct time as time
message as a message
from `table_name`
group by message, time

Upvotes: 0

Views: 47

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172994

Below is for BigQuery Standard SQL 

#standardSQL
SELECT 
  MIN(time) AS time,
  message
FROM `project.dataset.table_name`
GROUP BY message 
-- ORDER BY time

You can play with it using sample data from your question as below 

#standardSQL
WITH `project.dataset.table_name` AS (
  SELECT '10:00:01' time, 'hi' message UNION ALL
  SELECT '10:00:02', 'hi'
)
SELECT 
  MIN(time) AS time,
  message
FROM `project.dataset.table_name`
GROUP BY message 
-- ORDER BY time

this produces 

Row time        message  
1   10:00:01    hi

you can use MAX instead of MIN to produce below result 

Row time        message  
1   10:00:02    hi

Upvotes: 1

Tamir Klein
Tamir Klein

Reputation: 3632

Hope I understand your question it might need a bit clarification (Not sure what to or means in your question).

Anyway is this SQL what you are looking for:

select message as message,
time as time
from `table_name`
group by message 
order by message desc, time desc

Upvotes: 2

Related Questions