CODEWITHSUNDEEP
sqlgoogle-bigquery
Datasetter
Datasetter

Reputation: 121

Filter SQL data by repetition on a column

Very simple basic SQL question here.

I have this table:

Row Id __________Hour__Minute__City_Search   
1___1409346767__23____24_____Balears (Illes)     
2___1409346767__23____13_____Albacete    
3___1409345729__23____7______Balears (Illes)     
4___1409345729__23____3______Balears (Illes)     
5___1409345729__22____56_____Balears (Illes)

What I want to get is only one distinct row by ID and select the last City_Search made by the same Id. So, in this case, the result would be:

Row Id __________Hour__Minute__City_Search   
1___1409346767__23____24_____Balears (Illes)     
3___1409345729__23____7______Balears (Illes)

What's the easier way to do it?

Obviously I don't want to delete any data just query it.

Thanks for your time.

Upvotes: 1

Views: 230

Answers (3)

N.N.
N.N.

Reputation: 3172

two options to do it without join...

  1. use Row_Number function to find the last one

    Select * FROM (Select *, row_number() over(Partition BY ID Order BY Hour desc Minute Desc) as RNB from table) Where RNB=1

  2. Manipulate the string and using simple Max function

    Select ID,Right(MAX(Concat(Hour,Minute,RPAD(Searc,20,''))),20) From Table Group by ID

avoiding Joins is usually much faster...

Hope this helps

Upvotes: 0

Giannis Paraskevopoulos
Giannis Paraskevopoulos

Reputation: 18411

SELECT Row,
       Id,
       Hour,
       Minute,
       City_Search
FROM   Table T
       JOIN
       (
           SELECT MIN(Row) AS Row,
                  ID
           FROM   Table
           GROUP BY ID
       ) AS M
       ON M.Row = T.Row
          AND M.ID = T.ID

Upvotes: 1

Alfons
Alfons

Reputation: 531

Can you change hour/minute to a timestamp?

What you want in this case is to first select what uniquely identifies your row:

Select id, max(time) from [table] group by id

Then use that query to add the data to it. 

SELECT id,city search, time 
FROM (SELECT id, max(time) as lasttime FROM [table] GROUP BY id) as Tkey
INNER JOIN [table] as tdata
ON tkey.id = tdata.id AND tkey.lasttime = tdata.time

That should do it.

Upvotes: 1

Related Questions