Home » SQL & PL/SQL » SQL & PL/SQL » How to select unique columns - pls read to understand better
How to select unique columns - pls read to understand better [message #210115] Tue, 19 December 2006 08:03 Go to next message
sharath160
Messages: 9
Registered: August 2006
Junior Member
Hi,

Please go thru the below set of sql's.

I want unique orderNo values when selecting orderNo, shipdate. Various shipdates are present for same orderNo due to some reason. I am not much concerned about shipdate much though I want it. I am creating a view and then deleting the duplicate rows as below:

1)create or replace view VT1 as
SELECT orderNo,ShipDate FROM alo_car

2)delete from VT1 where rowid not in (select min(rowid) from VT1 group by orderNo)

3)SELECT * from VT1

I want a single SQL statement with above 3 functionalities. Can anybody help??
Re: How to select unique columns - pls read to understand better [message #210120 is a reply to message #210115] Tue, 19 December 2006 08:29 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
No, I don't think they can.

While you can mix DDL and DML to a certain extent (CREATE TABLE AS SELECT...), there's no way I know of to mix creating a view with any DML.

I think the question you need to answer is 'Why do I need to do these three as a single statment - why can't I just do all 3 statements one after the other?'
Re: How to select unique columns - pls read to understand better [message #210163 is a reply to message #210115] Tue, 19 December 2006 13:39 Go to previous message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
A view is simply a stored select. If you operate on the data in the view, it is actually updating the base tables (if you have a simple updateable view) . To view unique simply do the following


Please show an example of what you want returned. If your order numbers have multiple ship dates, how do you want to show it. For example if you wanted to only see the latest date, you could do the following.


SELECT orderNo,max(ShipDate)
FROM alo_car
group by orderNo;
Previous Topic: Creating procedure
Next Topic: varchar2 v/s char function
Goto Forum:
  


Current Time: Wed Dec 07 22:46:36 CST 2016

Total time taken to generate the page: 0.06304 seconds