First Record for similar rows

[ejaboneta]

I'm looking for the most efficient way to return the first record of a person based on a column holding a unix timestamp. I have about 10,000 rows so far with about 7000 unique 'firstname' and 'lastname' combinations. I want to return only the first record for each person.

 

 

This is what I have, but I'm afraid that since the records are not in order of the timestamp, that the earliest time will not be selected.

 

SELECT DISTINCT concat(firstname,' ',lastname) AS name, id,  timestamp FROM reports

 

 

Any help would be appreciated. Thanks.

[ejaboneta]

I finally got it to do what I want but It takes a whole minute to load. The query is actually going to be run 3 times on one script with different time periods so it would take 3 minutes for the page to load. I was also going to use it to generate customizable graphs but it'd be almost unbearable for my boss to wait a whole minute for each report to load. Can anyone think of a way to make it go faster?

 

I'll explain the table so you can understand it's purpose. This table holds a list of clients policies. I am trying to get count of how many new clients were enrolled during a time period and who their agent it. So first, it needs to get a list of client policies for the time period. Then it checks to see if this person was already a client before and if it is an old client, not count it.

 

'reports r' = the table where the policy information is held.

'reportagents a' = the table where agent names are held. Some agents listed in the reports table need to be counted under another name so thats why they are in a seperate table and not in a field.

 

SELECT 
    count(*) AS count, 
    a.user
FROM 
    reports r
LEFT JOIN 
    reportagents a ON r.agent = a.agent
WHERE (
    SELECT 
        min( q.submitted )
    FROM 
        reports q
    WHERE 
        concat( q.firstname, ' ', q.lastname ) = concat( r.firstname, ' ', r.lastname )
)
BETWEEN 
    '1262325600' AND '1277504715'
GROUP BY 
    a.user
LIMIT 
    0, 30 

[fenway]

I'm looking for the most efficient way to return the first record of a person based on a column holding a unix timestamp.

What does this actually mean???

[ejaboneta]

I tried to explain it in my second post.... I have a table of insurance policies... I want to only count the first policy on record for each person in the table. The purpose is to generate a report for the number of NEW clients.

 

The table has their name, and a unix timestamp of the policy date, among others.

[ejaboneta]

My last attempt at asking for help! If no one replies, I guess I'll give up and find some horrid manual way of doing it...

[fenway]

So you want the query to return on the first entry for each person?

[ejaboneta]

yes, but only if that first record is in the time period given.

[fenway]

Ok -- then simply join the table to itself and filter on the earliest time, with a where clause.

[ejaboneta]

and how would I do that? I did that with the query I posted but I think its checking every row for each row in the database. With 8000 records (8000*8000), it takes about a minute. I

[fenway]

That's because you're using GROUP BY -- and you don't need to.  And that's not joining to the table to itself.  Don't have time at the moment to write the query for you, though.

[ejaboneta]

That's because you're using GROUP BY -- and you don't need to.  And that's not joining to the table to itself.  Don't have time at the moment to write the query for you, though.

 

Well i also need it to count by agent. IE. Agent 1 had 30 new clients in May... Agent 2 had 25 new clients in May. How do i join the table to itself and would it be better to run an individual query for each agent? Thanks for you help. I really appreciate this.

[fenway]

Well, you can always run a scalar subquery on the final set just to get the counts, I suppose.