Máximo de grupos

Eu tenho uma tabela da qual eu estou tentando recuperar a última posição para cada segurança:

A mesa:

Minha consulta para criar a tabela: SELECT id, security, buy_date FROM positions WHERE client_id = 4

 +-------+----------+------------+ | id | security | buy_date | +-------+----------+------------+ | 26 | PCS | 2012-02-08 | | 27 | PCS | 2013-01-19 | | 28 | RDN | 2012-04-17 | | 29 | RDN | 2012-05-19 | | 30 | RDN | 2012-08-18 | | 31 | RDN | 2012-09-19 | | 32 | HK | 2012-09-25 | | 33 | HK | 2012-11-13 | | 34 | HK | 2013-01-19 | | 35 | SGI | 2013-01-17 | | 36 | SGI | 2013-02-16 | | 18084 | KERX | 2013-02-20 | | 18249 | KERX | 0000-00-00 | +-------+----------+------------+ 

Eu tenho mexido com versões de consultas com base nesta página , mas não consigo obter o resultado que estou procurando.

Aqui está o que eu tenho tentado:

 SELECT t1.id, t1.security, t1.buy_date FROM positions t1 WHERE buy_date = (SELECT MAX(t2.buy_date) FROM positions t2 WHERE t1.security = t2.security) 

Mas isso só me retorna:

 +-------+----------+------------+ | id | security | buy_date | +-------+----------+------------+ | 27 | PCS | 2013-01-19 | +-------+----------+------------+ 

Estou tentando obter a data de compra máxima / mais recente para cada segurança, para que os resultados tenham uma linha para cada segurança com a data de compra mais recente. Qualquer ajuda é muito apreciada.

EDIT: O ID da posição deve ser devolvido com a data de compra máxima.

Você pode usar essa consulta. Você pode alcançar resultados em 75% menos tempo. Eu verifiquei com mais dados. Subconsultas leva mais tempo.

 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; 

Link SQL-Fiddle

Você pode usar uma subconsulta para obter o resultado:

 SELECT p1.id, p1.security, p1.buy_date FROM positions p1 inner join ( SELECT MAX(buy_date) MaxDate, security FROM positions group by security ) p2 on p1.buy_date = p2.MaxDate and p1.security = p2.security 

Veja SQL Fiddle com Demo

Ou você pode usar o seguinte com uma cláusula WHERE :

 SELECT t1.id, t1.security, t1.buy_date FROM positions t1 WHERE buy_date = (SELECT MAX(t2.buy_date) FROM positions t2 WHERE t1.security = t2.security group by t2.security) 

Veja SQL Fiddle com Demo

Isso é feito com um simples grupo por. Você deseja agrupar pelos títulos e obter o máximo de buy_date. O SQL:

 SELECT security, max(buy_date) from positions group by security 

Note que isso é mais rápido que a resposta do bluefeet, mas não exibe o ID.

A resposta de @bluefeet tem mais duas maneiras de obter os resultados desejados – e o primeiro provavelmente será mais eficiente do que sua consulta.

O que eu não entendo é por que você diz que sua consulta não funciona. Parece muito bem e retorna o resultado esperado. Testado no SQL-Fiddle

 SELECT t1.id, t1.security, t1.buy_date FROM positions t1 WHERE buy_date = ( SELECT MAX(t2.buy_date) FROM positions t2 WHERE t1.security = t2.security ) ; 

Se os problemas aparecerem quando você adicionar a condição client_id = 4 , é porque você a adiciona somente em uma cláusula WHERE , enquanto você precisa adicioná-la em ambos:

 SELECT t1.id, t1.security, t1.buy_date FROM positions t1 WHERE client_id = 4 AND buy_date = ( SELECT MAX(t2.buy_date) FROM positions t2 WHERE client_id = 4 AND t1.security = t2.security ) ; 
 select security, max(buy_date) group by security from positions; 

é tudo que você precisa para obter a data de compra máxima para cada segurança (quando você diz em voz alta o que você quer de uma consulta e você inclui a frase “para cada x”, você provavelmente quer um grupo por x)

Quando você usa um group by , todas as colunas em sua seleção devem ser colunas que foram agrupadas por ou agregadas, por isso, se, por exemplo, você quisesse include o ID, provavelmente teria que usar uma subconsulta semelhante à que tinha antes, já que parece não haver nenhum agregado que você possa usar de forma razoável nos ids, e outro grupo lhe forneceria muitas linhas.