hi all,
Column | Type | Modifiers
--------+-----------------------+---------------------------------------------------
id | integer | not null default nextval('test_id_seq'::regclass)
f1 | character varying(32) |
f3 | character varying(32) |
f4 | character varying(32) |
f5 | character varying(32) |
f6 | character varying(32) |
f7 | character varying(32) |
f8 | character varying(32) |
f9 | character varying(32) |
f11 | character varying(32) |
f12 | character varying(32) |
f13 | character varying(32) |
f14 | character varying(32) |
f2 | character varying(32) |
f10 | character varying(32) |
512 | 432350221818600,355801020050525 | A | 43.28 | N | -80.07 | E | 08 | 0.05 | 1381.0 | 48.28 | 1/4042 | 24/0 | 082234 | 121109
513 | 432350221818600,355801020050525 | A | 43.28 | N | -80.07 | E | 08 | 0.05 | 1381.0 | 48.28 | 1/4042 | 24/0 | 082234 | 121109
521 | 432350221818600,355801020050524 | A | 43.28 | N | -80.07 | E | 08 | 0.05 | 1381.0 | 48.28 | 1/4042 | 24/0 | 192213 | 121109
523 | 432350221818600,355801020050524 | A | 43.28 | N | -80.07 | E | 08 | 0.05 | 1381.0 | 48.28 | 1/4042 | 24/0 | 092213 | 121109
577 | 432350221818600,355801020050525 | A | 43.28 | N | -80.07 | E | 08 | 0.05 | 1381.0 | 48.28 | 1/4042 | 24/0 | 082234 | 121109
...
I want to have a query that find the records of each client that are in ('') with the maximum f2(time) and f10(date) and if they were max and we have two similar records, it return a record with higher id
(it means for each client that are in ('','','',....) give us one record that have maximum date and time)
according to the above data, I want this result:
577 | 432350221818600,355801020050525 | A | 43.28 | N | -80.07 | E |
08 | 0.05 | 1381.0 | 48.28 | 1/4042 | 24/0 | 082234 | 121109
521 | 432350221818600,355801020050524 | A | 43.28 | N | -80.07 | E |
08 | 0.05 | 1381.0 | 48.28 | 1/4042 | 24/0 | 192213 | 121109
----------------------------------------------------------------------------------------------------------------------
what Do I...>>
select * from test where id in( SELECT id FROM test
WHERE f1 in
('432350221818600,355801020050524','432350221818600,355801020050525') GROUP BY f1
HAVING MAX(f10::int)>1 and MAX(f2::int)>1);
but this query raised an error:
ERROR: column "
test.id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select * from test where id in( SELECT id FROM test
but if I change Group BY f1,id , the result will be wrong , what can I do? or if I MAX(id).... result is wrong to :
577 | 432350221818600,355801020050525 | A | 43.28 | N | -80.07 | E |
08 | 0.05 | 1381.0 | 48.28 | 1/4042 | 24/0 | 082234 | 121109
523 | 432350221818600,355801020050524 | A | 43.28 | N | -80.07 | E |
08 | 0.05 | 1381.0 | 48.28 | 1/4042 | 24/0 | 092213 | 121109
Thanks in advace
--
Shahrzad Khorrami
