CODEWITHSUNDEEP
mysqlgoogle-bigquery
Esakkiammal Sudalaimuthu
Esakkiammal Sudalaimuthu

Reputation: 9

Fetch Unique record of one field and also combine with order by another field

I would like to fetch the unique record[studentName] from student collection based on joining date

Example: student [TableName]

studentName     joiningDate
Rosy             2018-06-25
Meera            2018-07-20                  
Rosy             2018-06-20

I have tried this below query but it does not worked.

select distinct(studentName) from student order by joiningDate

I want the result like this,

studentName     joiningDate
Rosy             2018-06-25
Meera            2018-07-20

Upvotes: 0

Views: 26

Answers (3)

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

You can use Group By with Max() aggregation function (to get recent joining date). Try the following query instead:

SELECT studentName, 
       MAX(joiningDate) AS joiningDate
FROM student 
GROUP BY studentName
ORDER BY joiningDate ASC

Upvotes: 0

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172994

Below is for BigQuery Standard SQL and works for any number of additional fields in that table w/o changing the query 

#standardSQL 
SELECT AS VALUE ARRAY_AGG(t ORDER BY joiningDate DESC LIMIT 1)[OFFSET(0)]
FROM `project.dataset.student` t
GROUP BY studentName

You can test, play with above using dummy data from your question: 

#standardSQL
WITH `project.dataset.student` AS (
  SELECT 'Rosy'studentName, '2018-06-25' joiningDate UNION ALL
  SELECT 'Meera', '2018-07-20' UNION ALL                  
  SELECT 'Rosy', '2018-06-20' 
) 
SELECT AS VALUE ARRAY_AGG(t ORDER BY joiningDate LIMIT 1)[OFFSET(0)]
FROM `project.dataset.student` t
GROUP BY studentName

with result 

Row studentName joiningDate  
1   Rosy        2018-06-25   
2   Meera       2018-07-20

Upvotes: 2

Usagi Miyamoto
Usagi Miyamoto

Reputation: 6289

Should try GROUP BY instead, like this:

SELECT studentName, MIN(joiningDate)
FROM student
GROUP BY studentName

Upvotes: 0

Related Questions