CODEWITHSUNDEEP
mysqlunique
user1502558
user1502558

Reputation: 23

SQL Query for columns with a unique value

I have a table which looks like this

    courseid     session_date     title                 published
    1            2012-07-01       Training Course A     0
    1            2012-07-02       Training Course A     0
    2            2012-07-04       Training Course B     1
    2            2012-07-07       Training Course B     1
    3            2012-07-05       Training Course C     1
    3            2012-07-06       Training Course C     1
    4            2012-07-07       Training Course D     1
    4            2012-07-10       Training Course D     1

The table has two entries for each ID and Title because the session_date column shows the start date and the end date of the course.

I am trying to create a query that will pull the next five courses without showing any courses in the past.

I have gotten this far

    SELECT session_date, title, courseid
    FROM table
    WHERE published = 1 AND session_date > DATE(NOW())
    ORDER BY session_date ASC LIMIT 0,5

This pulls rows from the table for the next five session-dates but it includes both start dates and finish dates whereas I need the next five courses ordered by start date.

I need to create a query that will pull the earliest session_date for each courseid but ignore the row with the latest session_date for that same courseid but I am at a complete loss of how to do this.

Any help or advice would be most gratefully received.

Upvotes: 2

Views: 158

Answers (3)

eggyal
eggyal

Reputation: 125835

If you group your results by course and select the MAX(session_date), you will get the latest of the dates associated with each course (i.e. the finish date):

SELECT   courseid, MIN(session_date) AS start_date
FROM     `table`
WHERE    published = 1
GROUP BY courseid
HAVING   start_date > CURRENT_DATE
ORDER BY start_date ASC
LIMIT    5

See it on sqlfiddle.

Upvotes: 2

Zane Bien
Zane Bien

Reputation: 23125

What you need to do is retrieve only the rows with the minimum session_date per courseid group and order by that resulting set:

SELECT
    b.*
FROM
    (
        SELECT courseid, MIN(session_date) AS mindate
        FROM tbl
        GROUP BY courseid
    ) a
INNER JOIN
    tbl b ON a.courseid = b.courseid AND a.mindate = b.session_date
WHERE
    b.session_date > NOW() AND
    b.published = 1
ORDER BY
    b.session_date
LIMIT 5

But a much better design would be to only have one row per courseid and have two columns specifying start and end dates:

tbl
------------------
courseid [PK]
start_date
end_date
title
published

Then you can simply do:

SELECT *
FROM tbl
WHERE start_date > NOW() AND published = 1
ORDER BY start_date
LIMIT 5

Upvotes: 1

Onkar
Onkar

Reputation: 3

Since values of all the columns in your SELECT clause are repeating, just use DISTINCT

SELECT distinct session_date, title, courseid FROM table WHERE published = 1 AND session_date > DATE(NOW()) ORDER BY session_date ASC LIMIT 0,5

Upvotes: 0

Related Questions