Thread: Select only first instances of a given criteria...

I seem to be the king of MySQL questions, given the lack of board activity...

Here's one for ya. I have a 'changelog' table in my database that tracks changes to the main entries. Any time an entry is updated, everything about it is copied to the changelog table and given a unique id. My question is as follows: how can I pull only the first instance of a given record in a query?

Here's an example. Say I have a dozen rows in this table, each having their own changelog_id. There is another field named 'id' that reflects the record's actual record in the main table. Now, out of this dozen, six have individual id numbers, two have the same id and the remaining four have the same id. So if I were to run the query:

Code:

SELECT count(*) as idx,id FROM changelog GROUP BY id ORDER BY idx ASC;

I'd end up with:

Code:

count	id
1	1
1	2
1	3
1	4
1	5
1	6
2	7
4	8

Which is well and good. Now to the part where I make this difficult.

There is another field in the table named 'input_date' which simply shows when the data was first input in the database. The problem comes in that each time the data is updated and the subsequent 'changelog' copy is written, this date is also copied. So if I were to look for all data with a given input_date, I would get every instance of it in the changelog. What I'm trying to do is find just the FIRST instance of it so I can see who it was entered by.

I thought I might be able to do something with DISTINCT, but that doesn't seem to work, at least not with the data I'm trying to read. I then though I could use MIN(), but that doesn't seem to work, either.

I would also like to mention there is another field named lastedit which is a datetime field. If there's some way to pull all records with the input_date of X and the smallest value for lastedit, I'd love to hear it.

not sure i'm understanding it all, but can't you just do ...

Code:

SELECT id, input_date, MIN(lastedit) 
FROM changelog 
GROUP BY id, input_date 
ORDER BY idx ASC;

didn't you say you only wanted the first record for an ID?

What I'm trying to do is find just the FIRST instance of it so I can see who it was entered by.

okay ... i'll admit it, i'm confused by your question.

You are probably going to need to use a subquery along the lines of this

Code:

Select A.id, A.old_id, A.input_date, A.lastedit
From daTable A
Join (
   Select input_date, min(lastedit) as lastedit
   From daTable 
   Group By input_date) as B
On A.input_date = B.input_date and A.lastedit = B.lastedit

Ok fudge, I misread what you were looking for. Lemme do a re-think

OK, let's try it this way and see if I understand now:

Code:

Select a.id, a.old_id, a.input_date, a.lastedit
   From dTable A
   Join  (Select old_id, input_date, min(lastedit)
           From daTable
           Group By input_date, old_id) as B
     on    A.input_date = B.input_date
      And A.old_id = B.old_id
      And A.lastedit = B.lastEdit