Need help with a query

[deepalijm]

Hi all,

I'm trying to
Select Top 1 value1 from table1
where CompID = 10
Order by mydate Desc

What this does is gives me the value of value1 for the max date.
Which works very well.

What I want to achieve is that get the max records for different compids = 7 or 9 or 10 etc. in the table.


Any ideas?

Thanks.

[deepuv04]

Hi all,

I'm trying to
Select Top 1 value1 from table1
where CompID = 10
Order by mydate Desc

What this does is gives me the value of value1 for the max date.
Which works very well.

What I want to achieve is that get the max records for different compids = 7 or 9 or 10 etc. in the table.


Any ideas?

Thanks.

Try the following query..... hope will work

SELECT value1 FROM table1 WHERE mydate IN (SELECT MAX(mydate ) FROM table1
GROUP BY CompID )

thanks

[deepalijm]

Thanks for the reply. I tried your query and it fails for the following example of data:

Table1:

ctid val dt
7 5 1/1/2006
7 8 4/1/2006
7 16 1/1/2007
9 4 2/24/2006
9 12 6/6/2006
9 6 12/12/2006
9 20 5/3/2007
10 3 12/12/2007
10 13 1/1/2007


I want to return:
ctid val dt
7 16 1/1/2007
9 20 5/3/2007
10 3 12/12/2007

Your query will fail and get the value:
10 13 1/1/2007


Please help.

Thanks,
Deepali.

Try the following query..... hope will work

SELECT value1 FROM table1 WHERE mydate IN (SELECT MAX(mydate ) FROM table1
GROUP BY CompID )

thanks

[deepuv04]

Thanks for the reply. I tried your query and it fails for the following example of data:

Table1:

ctid val dt
7 5 1/1/2006
7 8 4/1/2006
7 16 1/1/2007
9 4 2/24/2006
9 12 6/6/2006
9 6 12/12/2006
9 20 5/3/2007
10 3 12/12/2007
10 13 1/1/2007


I want to return:
ctid val dt
7 16 1/1/2007
9 20 5/3/2007
10 3 12/12/2007

Your query will fail and get the value:
10 13 1/1/2007


Please help.

Thanks,
Deepali.

Ya you are right....

now try the following

SELECT Table1.* FROM Table1 inner join
(SELECT MAX(mydate) dt,ID FROM Table1 GROUP BY ID) as T
on Table1.mydate = T.dt and T.id = Table1.id