SQL max and date trouble

[tridith]

I have one table, (table1).
Fields (date, asset_id, and location)..*and some other ones*

I want to get the most resent date and the location on that date, for each asset_id.

I was trying to use a MAX() on the date, in combination with a distinct on the asset_id. It didnt work.

How do I do this?

So for each distinct asset_id, i want to get its latest date.

Thanks.

Chad

 

[gmmastros]

Select Asset_Id, Max(DateColumn)
From   Table
Group By Asset_ID

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[tridith]

Thank you for the quick reply.

 

[tridith]

Sorry, I forgot to say that I also wanted to display the location as well.

When I add it to my select statement, i get an error saying it isnt a group by function, how do I fix that?

 

[fredericofonseca]

depending on what your requirements are exactly.

Select Asset_Id, location, Max(DateColumn)
From   Table
Group By Asset_ID, location

Select Asset_Id, location, Max_date
from table a
inner join
(Select Asset_Id, Max(DateColumn) as max_date
From   Table
Group By Asset_ID) as z
on z.asset_id = a.asset_id

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[tridith]

This is what i have but I keep getting an error saying "ORA-00905: Missing Keyword"
I dont know what I am missing

SELECT 
	"asset_type_id", 
	"location3distance", 
	"Max_Date"
	"location3"
FROM 
	QATECH_DATASTORE."cust_positions"  
inner join
(Select "asset_type_id", Max("date_time") as Max_Date
from QATECH_DATASTORE."cust_positions"
group by "asset_type_id") as z
on z."asset_type_id" = QATECH_DATASTORE."cust_positions"."asset_type_id"

Thank you!!

 

[tridith]

Oh, and that comma missing after "Max_Date", isnt the problem.

 

[carp]

I think you probably should put a comma after "Max_date".

 

[tridith]

lol, just beat ya on that one carp.

Ya, that isnt the problem

 

[carp]

No - that wasn't the error; but I think you may be setting up an unintentional column alias.
Still chewing on the missing word!

 

[carp]

It may or may not be the root of your problem, but it will make your code easier to read if you put an alias on your first table:

FROM QATECH_DATASTORE."cust_positions" a

which would allow your join condition to be

on z."asset_type_id" = a."asset_type_id"

I have run into other situations where Oracle will pitch a fit unless you alias all of your tables. Seems rather picky to me, but then again, I didn't invent Oracle so I don't get to make the rules!

 

[tridith]

Same error

SELECT
    "asset_type_id",
    "location3distance",
    "Max_Date",
    "location3"
FROM
    QATECH_DATASTORE."cust_positions" a
inner join
(Select "asset_type_id", Max("date_time") as Max_Date
from QATECH_DATASTORE."cust_positions"
group by "asset_type_id") as z
on z."asset_type_id" = a."asset_type_id"

 

[carp]

OK -
The immediate problem is the "AS" before z. AS may be used for column aliases, not table aliases.

After that is fixed, the double-quotes may create a new problem for you; but let's take it one at a time and see how it goes:

SELECT
    a."asset_type_id",
    a."location3distance",
    z."Max_Date",
    a."location3"
FROM
    QATECH_DATASTORE."cust_positions" a
inner join
(Select "asset_type_id", Max("date_time") as Max_Date
from QATECH_DATASTORE."cust_positions"
group by "asset_type_id") z
on z."asset_type_id" = a."asset_type_id";

 

[carp]

The following runs successfully on my DB (note that I omitted the schema name so I didn't have to set up a new user!):

SELECT
    a.asset_type_id,
    a.location3distance,
    z.Max_Date,
    a.location3
FROM
    cust_positions a
inner join
(Select asset_type_id, Max(date_time) as Max_Date
from cust_positions
group by asset_type_id) z
on z.asset_type_id = a.asset_type_id;

 

[tridith]

It is returning a lot of results.

I only want the most resent of the asset_type_id.

i dunno, maybe I am looking at this wrong.

Thank you so far for all ur help.

 

[tridith]

I know when I run this code, I only get 3 results returned, which is correct, but with that code, i am not getting all the fields that I need(location). But when I run that code that u made from above, I am getting a lot of results.

Select Asset_Id, Max(DateColumn)
From   Table
Group By Asset_ID

 

[tridith]

hmm, maybe that is right, I think someone screwed something up in the database that I am using, I think the reason that I am getting so many results.

Thank you again for all the help.

Sidenote: is this the right forum to be posting these types of questions in??

 

[fredericofonseca]

sorry. there is an error on my second join.

the join should be
on z.asset_type_id = a.asset_type_id
and max_date = date_time

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[fredericofonseca]

and no. the correct forum for Oracle questions is not this one.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[tridith]

all i can say is wow...Thanks