2

I have couple of columns in the table in Oracle DB, one is of varchar2 type while other is of date. I want to retrieve distinct ordered data

The following is the output of the below query 

select CS_ID,CS_Date from CSM order by CS_Date;

which yields:

CS_ID                    CS_Date
CS0000000001    29/03/15 14:23:25.872000000
CS0000000001    29/03/15 14:23:30.546000000
CS0000000001    29/03/15 14:23:30.577000000
CS0000000001    29/03/15 14:24:54.331000000
CS0000000001    29/03/15 14:39:51.881000000
CS0000000001    29/03/15 14:44:18.306000000
CS0000000001    29/03/15 14:44:27.372000000
CS0000000002    29/03/15 15:38:40.657000000
CS0000000003    29/03/15 18:41:15.409000000
CS0000000004    29/03/15 19:31:45.614000000

After adding distinct, following is the output. Ordered is not maintained after adding distinct

select distinct CS_ID from (
    select CS_ID,CS_Date from CSM order by CS_Date 
) v

which yields:

CS_ID
CS0000000002
CS0000000004
CS0000000003
CS0000000001

I am expecting the following output 

CS_ID
CS0000000001
CS0000000002
CS0000000003
CS0000000004

Could you please advise to how to maintain the order with distinct clause?

Jason Aller
  • 3,541
  • 28
  • 38
  • 38
Raja Reddy
  • 21
  • 2
  • Are you trying to order by the `cs_id` or the `cs_date`? In your example, you would appear to get the results you wanted by simply ignoring the `cs_date` and sorting on the `cs_id`. If you want to sort by the `cs_date`, do you want to sort by the `min(cs_date)` the `max(cs_date)` or by some other logic? What happens if one `CS0001` row comes after the first `CS0002` row but before the last `CS0002` row? – Justin Cave Mar 29 '15 at 17:33
  • @Jason I want to sort by cs_date, there can be other columns where i have to sort on. But end output should provide distinct CS_ID while retaining the order. – Raja Reddy Mar 29 '15 at 18:10
  • What order are you trying to retain? In your example, there are no overlaps so you can ignore the date and just sort on `cs_id`. My guess is that in your real table, you can have some `CS0001` rows that have a `cs_date` before the first `CS0002` rows and some that have a `cs_date` after the first `CS0002` row. If that's the case, which `cs_date` do you want to sort by? The `min(cs_date)`? The `max(cs_date)`? Something else? – Justin Cave Mar 29 '15 at 18:43
  • if anyone want to `group by + join rows data`, see https://stackoverflow.com/questions/12558509/concatenate-and-group-multiple-rows-in-oracle – yu yang Jian May 13 '21 at 08:37

2 Answers2

2

This will list each cs_id in order of the earliest cs_date for each.

select cs_id
from(
select cs_id,
       row_number() over(partition by cs_id order by cs_date) as rn,
       row_number() over(order by cs_date) as rn2
from csm
)
where rn = 1
order by rn2

(and, likewise, show each cs_id only once, achieving what you want with distinct)

Brian DeMilia
  • 13,103
  • 1
  • 23
  • 33
  • Thanks @BrianDeMilia, its working as expected. I have added one more column to sort and i require this for pagination. So i have modified the query as below and its working ok. Just want to know whether its efficient or there is any other way to do it. SELECT outer.* FROM (SELECT ROWNUM rnum, Inner.* FROM( (SELECT cs_id FROM (SELECT Cs_Id, Row_Number() Over(Partition BY Cs_Id Order By Cs_Date, Staff_Id DESC) AS Rn, row_number() over(order by cs_date, Staff_ID DESC) AS rn2 FROM csm ) WHERE Rn = 1 ORDER BY rn2 ) INNER )) Outer Where Outer.Rnum >=2 AND outer.rnum <=10 – Raja Reddy Mar 29 '15 at 22:11
  • @RajaReddy for pagination purposes that should be fine. – Brian DeMilia Mar 29 '15 at 22:19
0
select cs_id
from (select cs_id, min(cs_date) as min_cs_date
    from csm
    group by cs_id)
order by min_s_date
Shannon Severance
  • 18,025
  • 3
  • 46
  • 67