Groupwise maximum

by

You can use this query. You can achieve results in 75% less time. I checked with more data set. Sub-Queries takes more time.

SELECT p1.id, 
       p1.security, 
       p1.buy_date 
       FROM positions p1
left join
            positions p2
                on p1.security = p2.security
                   and p1.buy_date < p2.buy_date
      where 
      p2.id is null;

SQL-Fiddle link

More Related Contents:

  1. SQL select only rows with max value on a column [duplicate]
  2. How to create a MySQL hierarchical recursive query?
  3. How to reset AUTO_INCREMENT in MySQL
  4. MYSQL OR vs IN performance
  5. SELECTING with multiple WHERE conditions on same column
  6. MySQL Query GROUP BY day / month / year
  7. MySQL Error: : ‘Access denied for user ‘root’@’localhost’
  8. How to find gaps in sequential numbering in mysql?
  9. MySQL: Invalid use of group function
  10. Combining UNION and LIMIT operations in MySQL query
  11. MySQL comparison with null value
  12. MySQL JOIN the most recent row only?
  13. MySQL Group By and Sum total value of other column
  14. OUTPUT Clause in MySQL
  15. Search all columns of a table using a single where condition with single keyword in mysql
  16. What does SQL clause “GROUP BY 1” mean?
  17. Sorting by date & time in descending order?
  18. Case statement in MySQL
  19. MySQL, Concatenate two columns
  20. MySQL: Inner join vs Where [duplicate]
  21. Syntax error when using except in a query
  22. Selecting from two tables with inner join and limit [duplicate]
  23. Why the order of evaluation for expressions involving user variables is undefined?
  24. sql like operator on integer
  25. How to select data where a field has a min value in MySQL?
  26. MySQL – SELECT * INTO OUTFILE LOCAL ?
  27. How to SELECT by MAX(date)?
  28. The best way to remove value from SET field?
  29. get a recursive parent list
  30. mySQL query to search all tables within a database for a string?

Leave a Comment