aspose file tools*
The moose likes Oracle/OAS and the fly likes SQL question Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "SQL question" Watch "SQL question" New topic
Author

SQL question

Jackie Wang
Ranch Hand

Joined: Apr 18, 2002
Posts: 315
if I have a table with data:
(ID, date and description are all char)
ID Date Description
AA 2004-01-02 Des1
AA 2004-01-02 Des2
AA 2004-01-03 Des4
BB 2004-01-01 Des2
BB 2004-01-01 Des7
BB 2004-01-01 Des9
BB 2004-01-01 Des10
CC 2004-01-01 Des1
CC 2004-01-01 Des12

I just want to have ONE description per asset per day, I want the first record. How can I write the sql?
ID Date Description
AA 2004-01-02 Des1
AA 2004-01-03 Des4
BB 2004-01-01 Des2
CC 2004-01-01 Des1

thanks!
Julian Kennedy
Ranch Hand

Joined: Aug 02, 2004
Posts: 823
Hi Jackie,

I assume you're using Oracle. If so, this should do the trick:

It uses the rowid pseudo-column to resolve the duplicates. If you're not using Oracle you can achieve the same result using description instead of rowid, but it's obviously not as efficient to join on a char/varchar.

One small caveat is that I don't have an RDBMS here to test it, but I'm pretty confident it's correct. It should at least give you an idea of how to proceed.

You can probably also achieve the same effect in Oracle using a dynamic view (sub-query in the FROM clause) but it makes my brain hurt trying to visualise it without anywhere to test it!

Hope that helps.

Jules
Jackie Wang
Ranch Hand

Joined: Apr 18, 2002
Posts: 315
thanks
I am having the ERROR:
The following columns were not found in the contributing tables: rowid.

How to create a pesudo column? thanks!
Julian Kennedy
Ranch Hand

Joined: Aug 02, 2004
Posts: 823
Which RDBMS are you using? If you're not using Oracle, follow my alternative suggestion above. In Oracle you don't need to create rowid, it's just there.

There's a remote chance it could be insisting on rdt.rowid but I'd be surprised.

Jules
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: SQL question