Filter SQL data by repetition on a column

Question

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.

@RuslanVeselov Distinct won't help. His data in the first example are distinct. — Giannis Paraskevopoulos, Sep 02 '14 at 08:46
`Distinct` is not gonna solve the problem for him, as multiple city searches can occur per id — Alfons, Sep 02 '14 at 08:47
I think this: http://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column should help you. — trevorj, Sep 02 '14 at 09:59

score 1 · Answer 1 · answered Sep 02 '14 at 08:54

1

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.

answered Sep 02 '14 at 08:54

Alfons

511
4
17

It can be done without a timestamp aswell, but i think you'll be able to figure that one out yourself :D. – Alfons Sep 02 '14 at 08:55
I'm sorry I'm really new to SQL. The table i presented is already the result of a query. Can I reference that result (in your example [table]) so i can run your query after that? Should I substitute all the [table] by the nested query? – Datasetter Sep 02 '14 at 09:09
Note that I already changed the previous query so now I have only a Time column with hour*100+minute – Datasetter Sep 02 '14 at 09:10
You can do the trick i did to (almost?) every select query. its called a subquery. – Alfons Sep 02 '14 at 15:26
So basically you are dividing the data in two parts, the one with the keys and lasttime connection and the ones with all the data in it. That worked perfectly. Thank you. – Datasetter Sep 03 '14 at 08:13

score 1 · Accepted Answer · answered Sep 02 '14 at 08:54

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

score 0 · Answer 3 · answered Sep 04 '14 at 07:47

two options to do it without join...

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
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

Filter SQL data by repetition on a column

3 Answers3