group by with min

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

we have consumers with more than one insurance coverage with the primary
coverage being indicated by the smaller of two bill_seq field. When I run
the following query:

select min(bill_seq), consumerID from tblinsurance group by consumerid

there is no duplication just like I want but when I put the insurance name
in the query it gives me both insurances of the consumer not just the
primary. How can this be done to ony give one insurance per consumer.
 
Try something along the lines of
SELECT consumerId, insurance, bill_seq
FROM tblInsuranceGroup AS T1
WHERE bill_Seq IN (SELECT Min(bill_Seq) FROM tblInsuranceGroup WHERE
consumerId = T1.consumerId)

Hope This Helps
Gerald Stanley MCSD
 
Back
Top