Re: Query to return each value only once

On 28 Mar 2003 09:00:20 -0800, d_newsham_at_hotmail.com (D Newsham) wrote:

>I have a table that is structured as:
>
>coursedate_id number(4) PK
>session_no number(4)
>start_time date
>end_time date
>
>The data looks like this:
>
>10 202 21-Aug-2003 0800 21-Aug-2003 1200
>11 202 22-Aug-2003 0800 22-Aug-2003 1200
>12 202 23-Aug-2003 0800 23-Aug-2003 1200
>13 203 01-Sep-2003 1330 01-Sep-2003 1430
>14 204 02-Sep-2003 0830 02-Sep-2003 1200
>15 204 03-Sep-2003 0830 03-Sep-2003 1200
>
>I can run this query which will bring back only the first of repeated
>rows, but how can I change this to also return rows where the
>session_no is only used once?
>
>select session_no, start_time from coursedate c1
>where rowid not in
>(select min(rowid) from coursedate c2
>where c1.session_no = c2.session_no)
>
>returns:
>202 21-Aug-2003 0800
>204 02-Sep-2003 0830
>
>(session_no 203 is not returned because it only appears once in the
>table)
>
>My goal is to return each session number ONCE, with the start time, so
>the result would look like:
>
>202 21-Aug-2003 0800
>203 01-Sep-2003 1330
>204 02-Sep-2003 0830
>
>What do I need to add to this query to get these results?

select session_no, start_time from coursedate c1
>where start_time =
>(select min(start_time) from coursedate c2
where c1.session_no = c2.session_no)
>
>
>Thanks,
>Dina

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Fri Mar 28 2003 - 12:08:31 CST