How to distinct rows by 3 column and get sorted row

I have following table,

       sc_title      |      sc_date        | sc_coursecode | sc_courseno | sc_courseyear
------------------------------------------------------------------------------------------
    Open Water Diver | 2015-01-08 04:00:00 |       ow      |    05W      |    2015
    Open Water Diver | 2015-03-08 04:00:00 |       ow      |    05Z      |    2015
    Open Water Diver | 2015-04-08 04:00:00 |       ow      |    05W      |    2015
    Open Water Diver | 2015-05-18 04:00:00 |       ow      |    05W      |    2015
    Open Water Diver | 2015-05-09 04:00:00 |       ow      |    05Z      |    2015
    Open Water Diver | 2015-05-10 04:00:00 |       ow      |    05W      |    2015
    Open Water Diver | 2015-05-11 04:00:00 |       ow      |    05Z      |    2015
    Open Water Diver | 2015-05-12 04:00:00 |       ow      |    07W      |    2015
    Open Water Diver | 2015-05-13 04:00:00 |       ow      |    05Z      |    2015
    Open Water Diver | 2015-05-14 04:00:00 |       ow      |    07W      |    2015
    Open Water Diver | 2016-05-15 04:00:00 |       ow      |    07C      |    2016
        Bubble Maker | 2015-05-16 04:00:00 |       ow      |    07C      |    2015

I need those rows which title(sc_title) are like "Open Water Diver" and distinct by 3 column sc_coursecode, sc_courseno, sc_courseyear and also select only immediate future date row(sc_date). So my will be something like this,

       sc_title      |      sc_date        | sc_coursecode | sc_courseno | sc_courseyear
------------------------------------------------------------------------------------------
    Open Water Diver | 2015-05-10 04:00:00 |       ow      |    05W      |    2015
    Open Water Diver | 2015-05-09 04:00:00 |       ow      |    05Z      |    2015
    Open Water Diver | 2015-05-12 04:00:00 |       ow      |    07W      |    2015
    Open Water Diver | 2016-05-15 04:00:00 |       ow      |    07C      |    2016

I have written following query which is almost works but does not satisfy my final requirement.Getting that distinct row which contains immediate future date(sc_date).

SELECT sc_title,sc_date,sc_coursecode,sc_courseno,sc_courseyear 
FROM test_course WHERE DATE(sc_date) > DATE(NOW()) AND sc_title LIKE 'Open Water Diver%' 
GROUP BY sc_coursecode,sc_courseno,sc_courseyear ORDER BY sc_date ASC

If any one can help me then that would be great. Thanks in advance

Answers


Since you are looking for the immediate nearest date, use MIN on sc_date to pick the earliest one that is greater than DATE(NOW()):

SELECT
    sc_title
,   MIN(DATE(sc_date))
,   sc_coursecode
,   sc_courseno
,   sc_courseyear 
FROM test_course
WHERE DATE(sc_date) > DATE(NOW())
  AND sc_title LIKE 'Open Water Diver%' 
GROUP BY sc_coursecode, sc_courseno, sc_courseyear
ORDER BY sc_date ASC

Note that since you are not grouping by sc_title you would get an arbitrary match in the corresponding field when multiple different titles match the LIKE condition. To avoid this problem add sc_title to the GROUP BY list.


Need Your Help

Navigate form immediately in winforms

c# winforms

I'm developing a project via c# winforms.

Mock command line arguments for Python script with `optparse`?

python command-line-arguments optparse

A Python script that I want to use (called snakefood) is normally run from the commandline and takes commandline arguments, eg:

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.