Win a copy of Functional Reactive Programming this week in the Other Languages forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

SQL question

 
Jackie Wang
Ranch Hand
Posts: 315
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 823
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 315
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 823
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic