group by problem!

[shahrzad khorrami]

hi all,

in postgres


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


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists
Archive: http://www.houseoffusion.com/groups/sql/message.cfm/messageid:3250
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.6

[Maureen-2]

the where clause is returning ID so you need to Group by id, not F1

On Wed, Nov 4, 2009 at 1:30 AM, shahrzad khorrami
<shahrzad.khorrami@...> wrote:

> 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
>
>
>

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists
Archive: http://www.houseoffusion.com/groups/sql/message.cfm/messageid:3251
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.6