Home » SQL & PL/SQL » SQL & PL/SQL » Need a view that returns the most recent dates! Urgent, please help!
Need a view that returns the most recent dates! Urgent, please help! [message #214286] Mon, 15 January 2007 12:33 Go to next message
intermario_gr
Messages: 3
Registered: January 2007
Junior Member
Hi,

I'm designing a database about an art-gallery, I am working on a view that returns some results from two tables, CUSTOMER and TRANSACTION. The view's code so far as well as the results returned are shown in the picture attached.

/forum/fa/1989/0/

As you can see, for example, for WORKID=505 there are two rows. What I want to do however is only show, for each work, the row with the most recent date. That means that in the resulting table, there should be only one row for each WORKid, and that should be the one with the latest date.

Can you help me create the right view?
  • Attachment: DB_shot.JPG
    (Size: 26.17KB, Downloaded 836 times)

[Updated on: Mon, 15 January 2007 12:42]

Report message to a moderator

Re: Need a view that returns the most recent dates! Urgent, please help! [message #214295 is a reply to message #214286] Mon, 15 January 2007 13:24 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
select c.name,t.workid,max(t.purchasedate) purchasedate
from customer c, transaction t
where c.customerid = t.customerid
group by t.workid,t.name

Report message to a moderator

Re: Need a view that returns the most recent dates! Urgent, please help! [message #214309 is a reply to message #214295] Mon, 15 January 2007 15:50 Go to previous messageGo to next message
intermario_gr
Messages: 3
Registered: January 2007
Junior Member
Thanks for the reply Bill but your code doesn't do the trick..

The resulting table is somewhat closer to what I expected but not exactly.. Your code returns 7 rows as well, with works 505 and 525 still occuring twice but with the dates at a descending order.. However, I would like to only show one row for each workID, the one with the latest date. Take a look at the picture below, here's what your code does. Any other suggestions?

/forum/fa/1990/0/
  • Attachment: DB_shot1.JPG
    (Size: 28.32KB, Downloaded 711 times)

[Updated on: Mon, 15 January 2007 15:59]

Report message to a moderator

Re: Need a view that returns the most recent dates! Urgent, please help! [message #214312 is a reply to message #214286] Mon, 15 January 2007 16:00 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
The problem is that you have multiple names with the same workid. If you truly only want one row and don't care which name is used then do the following.



select max(c.name) name,t.workid,max(t.purchasedate) purchasedate
from customer c, transaction t
where c.customerid = t.customerid
group by t.workid;

Report message to a moderator

Re: Need a view that returns the most recent dates! Urgent, please help! [message #214314 is a reply to message #214312] Mon, 15 January 2007 16:06 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Did you mean something like this, perhaps?
SELECT c.name, t.workid, t.purchasedate
  FROM CUSTOMER c, TRANSACTION t
  WHERE c.customerid = t.customerid
    AND t.purchasedate = (SELECT MAX(t1.purchasedate)
                          FROM TRANSACTION t1
                          WHERE t1.workid = t.workid)

Report message to a moderator

Re: Need a view that returns the most recent dates! Urgent, please help! [message #214323 is a reply to message #214314] Mon, 15 January 2007 19:04 Go to previous message
intermario_gr
Messages: 3
Registered: January 2007
Junior Member
Bill B wrote on Tue, 16 January 2007 00:00
The problem is that you have multiple names with the same workid. If you truly only want one row and don't care which name is used then do the following.



select max(c.name) name,t.workid,max(t.purchasedate) purchasedate
from customer c, transaction t
where c.customerid = t.customerid
group by t.workid;


Well Bill, apparently that was the problem, I figured it out myself just before reading your reply. Although your solution returns just one row for each workID, it picks out the greater in the alphanumeric order, not the one the recent date belongs to! Thank you for the try anyway..


Littlefoot wrote on Tue, 16 January 2007 00:06
Did you mean something like this, perhaps?
SELECT c.name, t.workid, t.purchasedate
  FROM CUSTOMER c, TRANSACTION t
  WHERE c.customerid = t.customerid
    AND t.purchasedate = (SELECT MAX(t1.purchasedate)
                          FROM TRANSACTION t1
                          WHERE t1.workid = t.workid)



Littlefoot, that's exactly what I'm talking about! I wanted the most recent owner of each work.. Thanks man Cool

[Updated on: Mon, 15 January 2007 19:09]

Report message to a moderator

Previous Topic: script -> changing password and checking in all_users
Next Topic: %rowtype and views
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sun Dec 08 19:42:00 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.