Home arrow static arrow Java Programming [Archive] - sophisticated SQL question
Warning: Creating default object from empty value in /www/htdocs/w008deb8/wiki/components/com_staticxt/staticxt.php on line 51
Java Programming [Archive] - sophisticated SQL question
This topic has 20 replies on 2 pages.    « Previous | 1 | 2 |

Posts:106
Registered: 20.04.04
Re: sophisticated SQL question  
Jun 24, 2004 10:21 AM (reply 15 of 20)



 
i have to go. i'll going on watching in 12 hours...
 

Posts:1,125
Registered: 5/4/01
Re: sophisticated SQL question  
Jun 24, 2004 10:27 AM (reply 16 of 20)



 
you have to do a
select distinct pricefrom (  select price  from preise  where uid =?  and lid = ?  order by datum desc)where rownum =1

but then this is repeated for each product.

If worst comes to worst I can send u a script to demonstrate using max.

Ted.
 

Posts:36
Registered: 9/21/99
Re: sophisticated SQL question  
Jun 24, 2004 10:32 AM (reply 17 of 20)



 
The problem is that you want to get the max date for each product owned by each user. This query should do that:
   select x1.id, x1.prodnum, x1.price, x1.udate   from xxx 1,           (select userid, prodnum,max(uDate) udate           from xxx           group by userid, prodnum) x2   where x1.prodnum = x2.prodnum     and x1.udate = x2.udate     and x2.UserID = 10

It uses a sub-query to get the max dates for each prod/user combination, and then joins with that to limit the products to that latest for the user. This worked on Oracle, although I had to change some of the column names to avoid Oracle errors.

Glen
 

Posts:36
Registered: 9/21/99
Re: sophisticated SQL question  
Jun 24, 2004 10:34 AM (reply 18 of 20)



 
Sorry, missed an X in the first from:
   select x1.id, x1.prodnum, x1.price, x1.udate   from xxx x1,            (select userid, prodnum,max(uDate) udate            from xxx            group by userid, prodnum) x2   where x1.prodnum = x2.prodnum   and x1.udate = x2.udate   and x2.UserID = 10
 

Posts:364
Registered: 00-07-12
Re: sophisticated SQL question  
Jun 24, 2004 11:34 AM (reply 19 of 20)



 
try
select id, prodnum, price, date from xxx group by datehaving date = max(date)

sorry my bad, it should be:

select id, prodnum, price, date from xxx group by id, prodnum, price having date = max(date)

 

Posts:106
Registered: 20.04.04
Re: sophisticated SQL question  
Jun 25, 2004 5:35 AM (reply 20 of 20)



 
thanks a lot to you all.
gammloop
 
This topic has 20 replies on 2 pages.    « Previous | 1 | 2 |