Re: how to select distinct records

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Fri, 11 Nov 2005 08:42:44 +0100
Message-ID: <43744b73$0$8282$626a14ce@news.free.fr>

"Wally" <wallyraju_at_gmail.com> a écrit dans le message de news: 1131671909.089281.268650_at_g43g2000cwa.googlegroups.com...
|i have a table like one shown below
|
| start_date | end_date | traveller
| -----------------------------------------------
| 1/1/2005 | 10/10/2005 | a
| 1/1/2005 | 10/10/2005 | b
| 5/1/2005 | 11/10/2005 | c
| 2/1/2005 | 6/11/2005 | d
| 3/1/2005 | 11/23/2005 | e
| 3/1/2005 | 11/23/2005 | f
|
|
| I need to write a query that would give me only one record for any
| distinct date range. My results should be something like
|
| select distinct start_date, end_date, (first traveller found for that
| start_date and end_date) from table;
|
|
| start_date | end_date | traveller
| -----------------------------------------------
| 1/1/2005 | 10/10/2005 | a
| 5/1/2005 | 11/10/2005 | c
| 2/1/2005 | 6/11/2005 | d
| 3/1/2005 | 11/23/2005 | e
|
| or
|
| start_date | end_date | traveller
| -----------------------------------------------
| 1/1/2005 | 10/10/2005 | b
| 5/1/2005 | 11/10/2005 | c
| 2/1/2005 | 6/11/2005 | d
| 3/1/2005 | 11/23/2005 | f
|
| As you see I dont care about which traveller is returned for a
| particular date range (1/1/2005 - 10/10/2005) if it returns 'a' or
| 'b'. All i need is one record per date range.
|
| I know I can write a function that takes the start and end_date as
| parameters and returns the first record found by using the rownum, but
| is there a way to do this in plain SQL ?
|
| Thanks in advance,
| Wally
|

select start_date, end_date, traveller
from (select start_date, end_date, traveller,

             row_number() over (partitition by start_date, end_date) rn,
      from mytable)

where rn=1
/

But if you don't care about the traveller, why returning it? You can use:

select distinct start_date, end_date, 'i don''t care' traveller from mytable
/

Regards
Michel Cadot Received on Fri Nov 11 2005 - 01:42:44 CST