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.    1 | 2 | Next »

Posts:106
Registered: 20.04.04
sophisticated SQL question  
Jun 24, 2004 9:10 AM



 
my table "xxx":

id | UID | Price | Date...........| ProdNum
1.....10..... 20......2004-3-5......100
2.....10..... 22......2004-3-9......100
3.....10..... 40......2004-2-2......111
4.....11..... 30......2004-5-2......212

in every colume are redundant data allowed except in the key colume "id".

every user (UID) can own different products (ProdNum) but he also can own the same product several times with a different price and date each time.

i want to get the newest price for each product for a single user by SQL (access)

does someone know how to handle this problem?

thanks a lot
gammloop
 

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



 
my table "xxx":

id | UID | Price | Date...........| ProdNum
1.....10..... 20......2004-3-5......100
2.....10..... 22......2004-3-9......100
3.....10..... 40......2004-2-2......111
4.....11..... 30......2004-5-2......212

in every colume are redundant data allowed except in
the key colume "id".

every user (UID) can own different products (ProdNum)
but he also can own the same product several times
with a different price and date each time.

i want to get the newest price for each product for a
single user by SQL (access)

does someone know how to handle this problem?

thanks a lot
gammloop
select id, prodnum, pricefrom xxx x1where date = (  select max(date)  from xxx x2  where x2.date = x1.date  and x2.prodnum = x1.prodnum)and UID = ?

Ted.
 

Posts:106
Registered: 20.04.04
Re: sophisticated SQL question  
Jun 24, 2004 9:32 AM (reply 2 of 20)



 
wow, I'll try.
give me some minutes...
 

Posts:106
Registered: 20.04.04
Re: sophisticated SQL question  
Jun 24, 2004 9:39 AM (reply 3 of 20)



 
it does not work!
it put out every line not only the lines with the newest date for each prodNum.

i tried:
(LID = productnum, Preis = price, Datum = date)

rs = stmt.executeQuery(
"SELECT UID, LID, Preis "+
"FROM preise AS x1 "+
"where Datum = ( "+
"select max(Datum) "+
"FROM preise AS x2 "+
"where x2.Datum = x1.Datum "+
"and x2.LID = x1.LID ) "+
"and UID = " + UID
);
 

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



 
sorry made a slip up, should have been userid instead of date


rs = stmt.executeQuery(
"SELECT UID, LID, Preis "+
"FROM preise AS x1 "+
"where Datum = ( "+
"select max(Datum) "+
"FROM preise AS x2 "+
"where x2.Datum = x1.Datum "+
"and x2.LID = x1.LID ) "+
"and UID = " + UID
);

rs = stmt.executeQuery(
"SELECT UID, LID, Preis "+
"FROM preise AS x1 "+
"where Datum = ( "+
"select max(Datum) "+
"FROM preise AS x2 "+
"where x2.uid = x1.uid "+
"and x2.LID = x1.LID ) "+
"and UID = " + UID
);
 

Posts:106
Registered: 20.04.04
Re: sophisticated SQL question  
Jun 24, 2004 9:52 AM (reply 5 of 20)



 
mh, it does produce the same result as your last script with every line and not the lines with the newest date only.
 

Posts:364
Registered: 00-07-12
Re: sophisticated SQL question  
Jun 24, 2004 9:52 AM (reply 6 of 20)



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

Posts:1,125
Registered: 5/4/01
Re: sophisticated SQL question  
Jun 24, 2004 9:56 AM (reply 7 of 20)



 
mh, it does produce the same result as your last
script with every line and not the lines with the
newest date only.

doesnt on my mine!!!
 

Posts:106
Registered: 20.04.04
Re: sophisticated SQL question  
Jun 24, 2004 9:58 AM (reply 8 of 20)



 
i tried:
rs = stmt.executeQuery("select UID, LID, Preis, Datum from preise group by Datum having Datum = max(Datum)");

and i got a SQLException that "UID is not part of the function 'max-datum'"
 

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



 
I think you'll find UID is a reserved word.
 

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



 
i tried
rs = stmt.executeQuery("select LID, Preis, Datum from preise group by Datum having Datum = max(Datum)");
( -> without UID) and it throws the same SQL-Exception again!
 

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



 
in that case it said that LID is not part of the max-function
 

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



 
oops.

Can u use max in access? I'm use to Oracle.
 

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



 
i think so, i tried it in a simple query before.

would you know a solution without using MAX?
 

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



 
yeah, you'd have to use rownum though

hang on...
 
This topic has 20 replies on 2 pages.    1 | 2 | Next »