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
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.
Joined: Apr 18, 2002
thanks I am having the ERROR: The following columns were not found in the contributing tables: rowid.
How to create a pesudo column? thanks!
Joined: Aug 02, 2004
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.