Your regexp_replace() statement is invalid. It would work like this:
SELECT regexp_replace(tbl, '('''')$', '' , 'g')
FROM (
VALUES
('6''''')
,('6''''')
,('6''''')
,('20''''')
,('12''''')
,('18''''')
,('20''''')
,('8''''')
,('10''''')
,('''''')
,('''''')
) tbl(tbl)
Your SELECT INTO statement is invalid. It would look like this:
SELECT regexp_replace(tbl, '('''')$', '' , 'g')
INTO temp table_4
FROM temp_2
ORDER BY id;
But I would use neither.
SELECT INTO is discouraged. Only supported for historical reasons. Use CREATE TABLE AS instead, which is the SQL standard way. Per documentation:
CREATE TABLE AS is functionally similar to SELECT INTO. CREATE TABLE
AS is the recommended syntax, since this form of SELECT INTO is not
available in ECPG or PL/pgSQL, because they interpret the INTO clause
differently. Furthermore, CREATE TABLE AS offers a superset of the
functionality provided by SELECT INTO.
Bold emphasis mine.
For the presented examples, rtrim() is much simpler and faster:
CREATE TEMP TABLE table_4 AS
SELECT rtrim(tbl, '''') -- trim all trailing '
FROM temp_2
ORDER BY id;
You don't mention DBMS, so I'll assume it is one that supports window functions (DB2, Oracle, SQL-server, Postgres, etc)
select personid, xdate, seqnumber
from (
select personid, xdate, seqnumber
, row_number() over (partition by personid
order by xdate desc, seqnumber desc) as rn
from T
) as X
where rn = 1;
Edit: added sample data and result (http://sqlfiddle.com/#!4/73ad2/4)
create table T
( personid int
, xdate date
, seqnumber int );
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';
insert into t values (123, '2012-01-01', 0);
insert into t values (123, '2011-01-01', 0);
insert into t values (136, '2012-01-02', 1);
insert into t values (136, '2012-01-02', 0);
insert into t values (136, '2012-01-01', 0);
insert into t values (168, '2012-01-04', 2);
insert into t values (168, '2012-01-04', 1);
insert into t values (168, '2012-01-04', 0);
select personid, xdate, seqnumber
from (
select personid, xdate, seqnumber
, row_number() over (partition by personid
order by xdate desc, seqnumber desc) as rn
from T
) x
where rn = 1;
PERSONID XDATE SEQNUMBER
123 January, 01 2012 00:00:00+0000 0
136 January, 02 2012 00:00:00+0000 1
168 January, 04 2012 00:00:00+0000 2
Best Answer
In order to answer your question, I did the following (fiddle available here):
populate the table - added a couple of extra records for testing purposes.
And the ran the following query:
Result:
This provides the answer you require - there is an assumption that h_id is
UNIQUEor else you'll have to do aGROUP BYfid - but that's a different question.In future, could you please provide a fiddle with your table structures and your (presumably) sample data - it makes life much easier for those trying to help you and eliminates duplication of effort as well as providing a Single Source of Truth.
Also, although it doesn't make much difference in this case, could you also please provide your PostgreSQL version as a tag or in the question itself - it can be helpful!
p.s. welcome to the forum!