I have the following original table
What I want is to retrieve latest history_id with distinct new_id. As follow:
I found this distinct_but_only_one_column but it only contains how to retrieve distinct one column. Please help me out.
Asked
Active
Viewed 328 times
2
-
check your first result rows? i think its wrong otherwise show the your logical – Sathish Aug 15 '14 at 05:31
-
Are the values for `news_headline` correct where you have shown your desired output? I'm not certain that I see how these correlate. – mhawke Aug 15 '14 at 05:43
-
Are you looking for `DISTINCT ON`? – Craig Ringer Aug 15 '14 at 05:51
3 Answers
3
This will work, assuming your table is named x:
select * from x
where history_id in (
select max(history_id) from x group by news_id
);
Output:
history_id | news_id | news_headline
------------+---------+--------------------
1 | 5 | My test Headline
13 | 7 | My test Headline
3 | 4 | New Headline
4 | 6 | History Headline
This also works (and is simpler):
select distinct on (news_id) *
from x
order by news_id, history_id desc;
(but this never feels "right" to me)
mhawke
- 84,695
- 9
- 117
- 138
0
Try using this,
select max(history_id),news_id,newsheadline from
table1 group by news_id,newsheadline
-
This works only if `news_headline` is the same value for all `news_id` (which it might be, but then again, it might not be). – mhawke Aug 15 '14 at 05:50
0
Instead of using distinct on you can use a window function which is usually more flexible but a bit slower than using distinct on
select history_id, news_id, news_headline
from (
select history_id, news_id, news_headline,
row_number() over (partition by news_id order by history_id desc) as rn
from the_table
) t
where rn = 1
order by history_id;