OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » query??

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

query?? [message #39594]

Thu, 25 July 2002 05:16

saravanan
Messages: 70
Registered: October 2000

Member

HIREDATE SAL F
--------- ---------- -
20-FEB-81 1 y
20-FEB-81 2 n
08-SEP-81 4 y
19-APR-87 3 y
23-MAY-87 5 y
23-MAY-87 6 n

i have a result set as above .
in the above case there r 2 occassions when the dates r identical,but the corresponding F columns r different.
i want to retrieve all rows in which date column is unique and when it comes for a tie i want the 'n' flag to be selected

like 20-FEB-81 1 y
20-FEB-81 2 n in this case i want 'n' to be selected and other cases 'y'.

please help.
thankx

Report message to a moderator

Re: query?? [message #39601 is a reply to message #39594]

Thu, 25 July 2002 09:17

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

sql>select * from t
  2   order by hire_date, f;
 
HIRE_DATE       SAL F
--------- --------- -
20-FEB-81         2 n
20-FEB-81         1 y
19-APR-81         3 y
08-SEP-81         4 y
23-MAY-87         6 n
23-MAY-87         5 y
 
6 rows selected.
 
sql>select *
  2    from t
  3   where (hire_date, f) in
  4    (select hire_date, min(f)
  5       from t
  6      group by hire_date);
 
HIRE_DATE       SAL F
--------- --------- -
20-FEB-81         2 n
19-APR-81         3 y
08-SEP-81         4 y
23-MAY-87         6 n

Report message to a moderator

doubt [message #39605 is a reply to message #39601]

Thu, 25 July 2002 21:29

saravanan
Messages: 70
Registered: October 2000

Member

thanks todd for a great solution.
but is it advisable to use a min fn on a VARCHAR2.

thanks and regards

Report message to a moderator

Re: doubt [message #39616 is a reply to message #39601]

Fri, 26 July 2002 08:16

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

It is just fine to use MIN or MAX on VARCHAR2 datatypes, especially on a single-character column like you have there. One thing to be careful of: if you, for whatever reason, store a number in a VARCHAR2 column, be sure to convert it to a number when determining the max or min:

max(to_number(varchar2_column))

Report message to a moderator

Previous Topic:	SysDate
Next Topic:	Last returned SQLCODE?!?!???

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 05:15:23 CDT 2024