Mysql – how do i get rows for each month which contain the store with the highest number of purchases

greatest-n-per-groupMySQL

how do i get rows for each month which contain the store with the highest number of purchases?

my code is:

SELECT DATE_FORMAT(buyDate, '%M') as month, count(id) as numPurchases, store
from purchase, customerpurchases
where purchase.id = customerpurchases.purchaseid
group by month, store;

Best Answer

SELECT t1.*
FROM purchase t1
JOIN ( SELECT t2.`month`, MAX(t2.numPurchases) numPurchases
       FROM purchase t2
       GROUP BY t2.`month` ) t3 
    ON t1.`month` = t3.`month`
   AND t1.numPurchases = t3.numPurchases

Related Solutions

Postgresql – Consolidating large table

I want to group records that have the same combination of class, ip_address, and hostname, and keep the highest timestamp for each day from each group.

Not using the column name timestamp (like you shouldn't either). It's a reserved word in SQL and a basic type name in Postgres. Using ts instead.

The query is surprisingly simple with DISTINCT ON:

SELECT DISTINCT ON (class, ip_address, hostname, ts::date) *
FROM   agent_log
WHERE  ts < now() - interval '7 days'
ORDER  BY class, ip_address, hostname, ts::date, ts DESC;

Detailed explanation:
Select first row in each GROUP BY group?

Oracle – Get Row with the Biggest Value from Each Group

I think something like that should work

SELECT b.* FROM 
(
    SELECT a.*, 
     ROW_NUMBER() OVER (PARTITION BY a.COD_VOO_REGULAR ORDER BY a.ATRASO DESC ) AS rn
    FROM 
    (
      SELECT  VIAGEM.IDVOOREGULAR COD_VOO_REGULAR, 
            VIAGEM.DATAPARTIDA DATA,
            VOO.ORIGEM, 
            VOO.DESTINO, 
            (((EXTRACT (DAY FROM (DATACHEGADA-DATAPARTIDA))*24*60*60 +
               EXTRACT (HOUR FROM (DATACHEGADA-DATAPARTIDA))*60*60 +
               EXTRACT (MINUTE FROM (DATACHEGADA-DATAPARTIDA))*60)/60)-VOO.DURACAO) ATRASO
     FROM   VIAGEM, 
            VOO_REGULAR, 
            VOO
    WHERE   VIAGEM.IDVOOREGULAR=VOO_REGULAR.ID AND 
            VOO_REGULAR.IDVOO=VOO.ID
    )a
) b
WHERE rn = 1 ;

Best Answer

Related Solutions

Postgresql – Consolidating large table

Oracle – Get Row with the Biggest Value from Each Group

Related Question