CODEWITHSUNDEEP
mysql
SNAG
SNAG

Reputation: 2113

Mysql Query for getting first instance of a element

I have a table that has data like below(sorted by date)

COL_A | COL_B

Item1 | Date1
Item2 | Date1
Item2 | Date2
Item1 | Date3
Item3 | Date3

Now I want a query that will return the first instance of every Item ie the result set should be like;

COL_A | COL_B

Item1 | Date1
Item2 | Date1
Item3 | Date3

How to do this?

Upvotes: 0

Views: 1492

Answers (2)

kaj
kaj

Reputation: 5251

If all you need is the first instance of each and no further information then its a simple GROUP BY with MIN:

select Col_A, min(Col_B) MinColB
from tbl
group by Col_A

If you want further information from the table having found the first instance:

select t1.Col_A, t1.Col_B, t1.otherField...
from tbl t1
  inner join (select Col_A, min(Col_B) MinColB
              from tbl
              group by Col_A) t2 on t2.Col_A = t1.Col_A and t2.MinColB = t1.Col_B

You use the derived table to find the first date per Col_A and then join back to the table to retrieve further details

Upvotes: 1

Michael Berkowski
Michael Berkowski

Reputation: 270775

This is a rudimentary group aggregate on MIN(COL_B), provided COL_B is a proper datetime type:

SELECT
  COL_A,
  MIN(COL_B) AS FIRST_COL_B
FROM tbl
GROUP BY COL_A
ORDER BY COL_A ASC

I recommend reviewing the MySQL manual on aggregate functions and GROUP BY.

Upvotes: 1

Related Questions