A group by / join question.

[Sxooter]

OK, My brain if fragged today, and I know this must be simple, but I'm not getting it.

Here's the table:

select * from padr;
prodcode | liccount | date
----------+----------+---------------------
A | 1 | 2003-05-06 07:00:00
A | 5 | 2003-05-06 07:00:00
A | 4 | 2003-05-06 12:30:00
A | 5 | 2003-05-06 13:30:00
A | 2 | 2003-05-06 03:30:00
A | 1 | 2003-05-06 13:30:00
B | 4 | 2003-05-06 14:01:00
B | 1 | 2003-05-06 12:30:00
B | 2 | 2003-05-06 10:30:00
B | 3 | 2003-05-06 09:30:00
B | 3 | 2003-05-06 06:30:00

Now, what I want is the date field that matches the largest liccount for each prodcode. I can get the largest liccount for each prodcode with

select prodcode,max(liccount) from padr p1 group by prodcode;

but how do I get the associated date field to go with it?

[Lars_Berg]

select * from padr p
where liccount in (
select max(liccount) 
from padr 
where prodcode = p.prodcode)

[Sxooter]

Yeah, the query I came up with was:

select * from (
  select prodcode, 
  max(liccount) as maxl
  from padr 
  group by prodcode
)
as p1, 
padr p2  

where 
  p1.prodcode=p2.prodcode and 
  p1.maxl=p2.liccount;

Now the problem is that the user only wants the latest date in each one.

Note I have a tendency to avoid in() since Postgresql can be slow at them, so my queries tend to rely more on subselects and such.

[Sxooter]

Like all good user requirements, this has dribbled in a drop at a time. Here's what we really need.

The dates run over time, 2002-05-05... 06, 07 etc..

What we want is the date field for the highest liccount for each day for each prodcode.

ugh...