CODEWITHSUNDEEP
sqlpostgresql
ryan munene
ryan munene

Reputation: 43

Is there a way to limit to only 1 row per column value in a table?

I'm filtering some data where say table.x has the following structure

column1 | c.2 |column3

0.0.0.0 | 20 | 2019-04-29 14:55:52
0.0.0.0 | 10 | 2019-04-29 14:45:52
0.0.0.0 | 50 | 2019-04-29 14:35:52
0.0.0.0 | 50 | 2019-04-29 14:25:52
0.0.0.0 | 90 | 2019-04-29 14:15:52
0.0.0.1 | 40 | 2019-04-29 14:05:52
0.0.0.1 | 40 | 2019-04-29 13:45:52
0.0.0.1 | 70 | 2019-04-29 13:30:52
0.0.0.4 | 20 | 2019-04-29 13:25:52

I would like the result set to return as

0.0.0.0 | 20 | 2019-04-29 14:55:52
0.0.0.1 | 40 | 2019-04-29 14:05:52
0.0.0.4 | 20 | 2019-04-29 13:25:52

Upvotes: 1

Views: 1364

Answers (5)

Sayfulla Koneev
Sayfulla Koneev

Reputation: 1

Try to use window function

select 
    column1, column2, column3
from (
    SELECT 
        column1, column2, column3,
        row_number() over ( partition by column1 ) pbg
    FROM tablename
) aaaa
where aaaa.pbg = 1

Upvotes: 0

Youcef LAIDANI
Youcef LAIDANI

Reputation: 59950

What about using DISTINCT with one column like so :

SELECT DISTINCT ON (column1) column1, column2, column3 FROM table_name

Note c.2 is not a valid column name.

Here is a demo online https://rextester.com/FBIS74019

Upvotes: 3

Nikolas
Nikolas

Reputation: 44368

Try the following SQL code:

SELECT max(column1), column2, max(column3)   --maximum of IP address and time
FROM yourTable
GROUP BY column1                             --grouped by IP address

The result is:

max(column1)  |  column2  |  max(column3)
------------------------------------------------
0.0.0.0          20          2019-04-29T14:55:52Z
0.0.0.1          40          2019-04-29T14:05:52Z
0.0.0.4          20          2019-04-29T13:25:52Z

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133360

You could usea inner join on max col3 group by column1 

select * from my_table m
inner join  (
  select  column1, max(column3) col3
  from my_table 
  group by column1 
  ) t on t. column1 = m.column1 and t.col3 = m.column3

Upvotes: 1

forpas
forpas

Reputation: 164069

You need to get the max column3 for each column1 by grouping and then join to the table:

select t.*
from tablename t inner join (
  select column1, max(column3) column3
  from tablename
  group by column1
) g on g.column1 = t.column1 and g.column3 = t.column3

Upvotes: 1

Related Questions