1

Given this set...

Site, Date
4   10/10/18
3   10/10/18
4   07/10/18
5   12/10/18
3   11/10/18
5   13/11/18

I want to order it such that the rows are ordered by site and the site groups are ordered by MIN date.

i.e.

Site, Date
4   07/10/18
4   10/10/18
3   10/10/18
3   11/10/18
5   12/10/18
5   13/11/18

I have tried using...

SELECT

    RANK() OVER (PARTITION BY Site ORDER BY (Date)) AS GroupRank,
    a.*
FROM

    (select * from
    (select 4 AS Site,   '10/10/18' as date union
    select 3,   '10/10/18' union
    select 4,   '07/10/18'  union
    select 5,   '12/10/18'  union
    select 3,   '11/10/18'  union
    select 5,   '13/11/18'  as date ) b) a
    order by GroupRank

The results don't make sense.

1   3   10/10/18
1   4   07/10/18
1   5   12/10/18
2   5   13/11/18
2   4   10/10/18
2   3   11/10/18

It's supposed to be partitioning by site but it's given the same rank to rows with different sites.

  • A site with a greater value may have a lower date than a site with a lower value and the query needs to order the site groups by date. – Ian Warburton Oct 9 '18 at 15:14
2

Something like this:

SELECT

    min(date) OVER (PARTITION BY Site) AS MinDate,
    a.*
FROM

    (select * from
    (select 4 AS Site,   '10/10/18' as date union
    select 3,   '10/10/18' union
    select 4,   '07/10/18'  union
    select 5,   '12/10/18'  union
    select 3,   '11/10/18'  union
    select 5,   '13/11/18'  as date ) b) a
    order by MinDate, Site
1

That is another case of max-by-group problem.

SELECT w.Site
     , w.Date
  FROM table AS w
  JOIN ( SELECT Site -- fetch MIN Date for each Site, sort them and enumerate
              , MIN(Date) AS mindate
              , IF(@cnt = '', 1, @cnt := @cnt + 1) AS cnt
           FROM table
          GROUP BY Site
          ORDER BY mindate ASC
       ) AS z ON z.Site = w.Site
 ORDER BY z.cnt ASC   -- Now order the result by numeration of MINs
        , w.Date ASC  -- Then by Date inside that groups
;
0

Can't figure out how to do this using OVER clause. This joins the rows to a subquery that derives the order.

SELECT
    a0.Site,
    a0.Date
FROM
    (SELECT 4 AS Site, '10/10/18' AS Date UNION
    SELECT 3,   '10/10/18' UNION
    SELECT 4,   '07/10/18' UNION
    SELECT 5,   '12/10/18' UNION
    SELECT 3,   '11/10/18' UNION
    SELECT 5,   '13/11/18') a0
INNER JOIN
    (SELECT
        ROW_NUMBER() OVER(ORDER BY MIN(date)) as RowNumber,
        Site
    FROM
        (SELECT 4 AS Site, '10/10/18' AS Date UNION
        SELECT 3,   '10/10/18' UNION
        SELECT 4,   '07/10/18' UNION
        SELECT 5,   '12/10/18' UNION
        SELECT 3,   '11/10/18' UNION
        SELECT 5,   '13/11/18') a1
    GROUP BY
        Site) b
ON
    a0.site = b.site
ORDER BY
    RowNumber

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.