I want to fetch the first and last record of every month in sql but my query give the results below and here is my query

SELECT DISTINCT month, amount, 
       MIN(date) OVER (PARTITION BY month ORDER BY utility.month) 
FROM 
    utility;

results of the query above

CodePudding user response：

Try using MIN and MAX at the same time together with GROUP BY.

Check this from W3Schools.

The MIN() function returns the smallest value of the selected column.

The MAX() function returns the largest value of the selected column.

Try this code: SELECT DISTINCT month, amount, MIN(date), MAX(date) FROM utility GROUP BY month;

CodePudding user response：

You can get the MIN() and MAX() value first, turn into a subquery then join utility table twice to get the amount corresponding to the extracted dates, like this:

SELECT v.month,
       v.mindt,
       u1.amount,
       v.maxdt,
       u2.amount
   FROM
(SELECT month, 
       MIN(date) mindt, MAX(date) maxdt
FROM 
    utility
GROUP BY month) v
JOIN utility u1 ON u1.date=v.mindt
JOIN utility u2 ON u2.date=v.maxdt
;

That will give result something like this:

Or you can modify the subquery to do UNION ALL, join utility once and return all in just the original 3 columns:

SELECT v.month,
       v.minmaxdt,
       u.amount
   FROM
(SELECT month, 
        MIN(date) minmaxdt
    FROM utility
   GROUP BY month 
 UNION ALL
 SELECT month, 
        MAX(date) 
    FROM utility
    GROUP BY month
 ) v
JOIN utility u ON u.date=v.minmaxdt
ORDER BY v.month, v.minmaxdt;

That will give result something like this:

Demo fiddle