CODEWITHSUNDEEP
sqldb2
MrSimpleMind
MrSimpleMind

Reputation: 8597

How to select this rows based on two columns and a min function

I greatly appreciate all help for writing a select query for this.

MyTable 

 sw_id | url_id |  open_date   | valid
----------------------------------------
  101  |  com   |  2013-01-01  |  1
  202  |  net   |  2013-01-02  |  1
  202  |  net   |  2013-02-02  |  1 
  303  |  com   |  2013-01-03  |  1 
  303  |  com   |  2013-02-03  |  1 
  303  |  com   |  2013-03-03  |  1 
  404  |  org   |  2013-01-04  |  1 
  404  |  org   |  2013-02-04  |  1 
  404  |  gov   |  2013-02-04  |  1
  404  |  gov   |  2013-04-04  |  1 
  ...

We need to find the valid (valid=1) rows where same sw_id, url_id got less open_date.

select query should output the rows from above something like this:

Query output 

 sw_id | url_id |  open_date   | valid  |  min_open_d 
-----------------------------------------------------
  202  |  net   |  2013-02-02  |  1     |  2013-01-02
  303  |  com   |  2013-02-03  |  1     |  2013-01-03
  303  |  com   |  2013-03-03  |  1     |  2013-01-03
  404  |  org   |  2013-02-04  |  1     |  2013-01-04
  404  |  gov   |  2013-04-04  |  1     |  2013-02-04

As you can see, we select only the rows that have open_date higher than the lowest, min(open_date).

I hope you understand the problem from the description above.

Upvotes: 2

Views: 1146

Answers (3)

user1919238
user1919238

Reputation:

This problem can be solved a bit more elegantly with OLAP functions:

select * from (
    select sw_id, 
           url_id, 
           open_date, 
           valid, 
           min(open_date) over (partition by sw_id,url_id) as min_open_d
       from MyTable
       where valid = 1
    )
    where open_date <> min_open_d;

This should also be more efficient than a subquery.

Upvotes: 0

RThomas
RThomas

Reputation: 10882

Your sample and explanation leaves a little bit open to interpretation but something like this should be close.

SELECT  sw_id ,
        url_id ,
        open_date ,
        valid ,
        ( SELECT    MIN(open_date)
          FROM      mytable mt2
          WHERE     mt2.sw_id = mt1.sw_id
                    AND mt1.url_id = mt2.url_id
          GROUP BY mt2.sw_id, mt2.url_id
        ) AS min_open_d
FROM    mytable mt1
WHERE   valid = 1 AND mt1.open_date >          
        ( SELECT    MIN(open_date)
          FROM      mytable mt3
          WHERE     mt3.sw_id = mt1.sw_id
                    AND mt1.url_id = mt3.url_id
          GROUP BY mt3.sw_id, mt3.url_id
        )

Upvotes: 1

Ruslan Osipov
Ruslan Osipov

Reputation: 5843

It would be good if you showed your own effort here as well, but basically subqueries is one of the (easiest for newbie to understand) ways to do it.

SELECT
    sw_id,
    url_id,
    open_date,
    valid,
    (
        SELECT MIN(open_date)
        FROM MyTable t2
        WHERE t2.sw_id = t1.sw_id GROUP BY t2.sw_id
    ) AS min_open_d
FROM
    MyTable t1
WHERE
    valid = 1
ORDER BY
    open_date DESC

Upvotes: 1

Related Questions