• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

SQL question

 
Ranch Hand
Posts: 315
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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!
 
Ranch Hand
Posts: 823
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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!
 
JulianInactive KennedyInactive
Ranch Hand
Posts: 823
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
reply
    Bookmark Topic Watch Topic
  • New Topic