Re: Select Distinct

[rob@xxxxxxxxx]

Ending with that where clause isn't cutting it for me.
Token <END-OF-STATEMENT> was not valid. Valid tokens: < > = <> <= !<

select * from duh 
...1....+....2....+....3....+....4....+....5....+....
COMPANY          VENDOR           PAYEE   INVOICEDATE
      1               1               1   2006-09-01 
      1               1               1   2006-10-01 
      1               1               2   2006-10-01 
      1               1               1   2006-10-09 
      1               1               2   2006-10-02 


select distinct company, vendor, payee, invoicedate
from duh 
where (company, vendor, invoicedate) in 
 (select company, vendor, max(invoicedate) 
  from duh 
  group by company, vendor) 
...1....+....2....+....3....+....4....+....5....+....
COMPANY          VENDOR           PAYEE   INVOICEDATE
      1               1               1   2006-10-09 
**  End of data  ******** 

Let's add a few more rows...
select * 
from duh 
order by company, vendor, payee, invoicedate desc
...1....+....2....+....3....+....4....+....5....+....
COMPANY          VENDOR           PAYEE   INVOICEDATE
      1               1               1   2006-10-09 
      1               1               1   2006-10-01 
      1               1               1   2006-09-01 
      1               1               2   2006-10-09 
      1               1               2   2006-10-02 
      1               1               2   2006-10-01 
      1               2               2   2006-10-02 
**  End of data  ******** 
Why did I add a few more?
1 - I want you to think about what to do if the max has two payees for the 
same company and vendor for the same last invoice date.
2 - Also wanted to add a second vendor.

And now let's drop the distinct
select company, vendor, payee, invoicedate
from duh 
where (company, vendor, invoicedate) in 
 (select company, vendor, max(invoicedate)
  from duh 
  group by company, vendor) 
...1....+....2....+....3....+....4....+....5....+....
COMPANY          VENDOR           PAYEE   INVOICEDATE
      1               2               2   2006-10-02 
      1               1               1   2006-10-09 
      1               1               2   2006-10-09 
**  End of data  ******** 

Either you want to know the payee, or you don't - like another sql 
question from this week (regarding employee number and email address).  I 
could play around with max or min on payee also.  Or, if it really doesn't 
matter, just stick in a random number.

Rob Berendt