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

Question

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

Why those rows and not the others with the same IPs? You need to be able to describe that for the database to provide them. — Richard Huxton, Apr 30 '19 at 18:33
RichardHuxton I need one for each ip there can be multiple ip's — ryan munene, Apr 30 '19 at 18:36
Take a look here: https://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group — Bor Laze, Apr 30 '19 at 19:19

Youcef LAIDANI · Answer 1 · 2019-04-30T18:59:56.530

3

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

edited Apr 30 '19 at 18:59

answered Apr 30 '19 at 18:36

Youcef LAIDANI

55,661
15
90
140

score 1 · Accepted Answer · answered Apr 30 '19 at 18:36

1

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

answered Apr 30 '19 at 18:36

forpas

160,666
10
38
76

This will of course give you multiple rows when you have duplicate timestamps in column3. That might or might not be what you intended. If it is what you intended, please add a clarification to your question or a comment here if you can't edit the question. – Richard Huxton Apr 30 '19 at 19:13
That's was not the intention, I need just one – ryan munene May 02 '19 at 07:43

score 1 · Answer 3 · answered Apr 30 '19 at 18:36

1

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

answered Apr 30 '19 at 18:36

ScaisEdge

131,976
10
91
107

Nikolas Charalambidis · Answer 4 · 2019-04-30T18:43:13.990

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

score 0 · Answer 5 · answered May 01 '19 at 17:25

0

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

answered May 01 '19 at 17:25

Sayfulla Koneev

1
2

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

5 Answers5