Oracle sql functions

Question

I have a table with a column called Type, which can have three values (name1, name2, name3).

Can I write a query which first returns the records with Type = name1 and then the rows with values name2 and name3 with a WHERE clause, so I can filter them by CreationDate for instance?

That means, return for day 01/01/2000:

row  'name1'
row  'name1'
(rest of the rows)

Id   Type    CreationDate  
1,  'name1', '2000/01/01'  
8,  'name1', '2000/01/01'  
18, 'name3', '2000/01/01'  
82, 'name2', '2000/01/01'  
11, 'name2', '2000/01/01'  
12, 'name3', '2000/01/01'  
2,  'name1', '2000/01/02'  
4,  'name1', '2000/01/02'  
98, 'name2', '2000/01/02'

For every day, get records of type 'name1' first and then the rest of the types with just no order.

Thank you! Udo.

Parris Varney · Accepted Answer · 2010-11-16 16:45:18Z

5

You can order by create date then type. If you need to define the order of the type you can use a case.

SELECT id, Type, CreationDate
FROM  "table"
ORDER BY CreationDate ASC,
         CASE WHEN Type = 'name1' THEN 1
              ELSE 2
         END ASC

answered Nov 16, 2010 at 16:45

Parris Varney

11.5k12 gold badges49 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jagmag · Accepted Answer · 2010-11-16 16:50:45Z

0

PMV's answer above should work fine Putting this in just as an alternate option.

SELECT id, Type, CreationDate
FROM  "table" 
ORDER BY CreationDate, decode(Type , 'name1', 0, 1), Type ASC

answered Nov 16, 2010 at 16:50

Jagmag

10.4k1 gold badge37 silver badges60 bronze badges

Comments

Leigh Riffel · Accepted Answer · 2010-11-16 18:41:26Z

It sounds like you might want to prioritize the ordering only based on Date and type='name1' and specifically need the remaining order to be random in nature. If this is the case you can add dbms_random.value as the third item in the ORDER BY in either PMV or Insane's query. Sample table and example follow:

create table Table1 (Id Number(2), Type Varchar2(5), CreationDate Date);
insert into Table1 Values ( 1,'name1',to_date('01/01/2000','MM/DD/YYYY'));
insert into Table1 Values ( 8,'name1',to_date('01/01/2000','MM/DD/YYYY'));
insert into Table1 Values (18,'name3',to_date('01/01/2000','MM/DD/YYYY'));
insert into Table1 Values (82,'name2',to_date('01/01/2000','MM/DD/YYYY'));
insert into Table1 Values (11,'name2',to_date('01/01/2000','MM/DD/YYYY'));
insert into Table1 Values (12,'name3',to_date('01/01/2000','MM/DD/YYYY'));
insert into Table1 Values ( 2,'name1',to_date('01/02/2000','MM/DD/YYYY'));
insert into Table1 Values ( 4,'name1',to_date('01/02/2000','MM/DD/YYYY'));
insert into Table1 Values (98,'name2',to_date('01/02/2000','MM/DD/YYYY'));

SELECT Id, Type, CreationDate FROM Table1
ORDER BY CreationDate, decode(Type , 'name1', 0, 1) ASC, dbms_random.value;

Collectives™ on Stack Overflow

Oracle sql functions

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related