Very Computer

by **Uri Diman** » Wed, 27 Feb 2002 22:41:16

hello!
this is result from my select query
select * from table WHERE prjnum = 52 and vb1 is not null
prjnum setid insertid timenum top_m vb1
vb2 vb3
----------- -------- ----------- ------------------------ ------------------
------ ------------------------ ------------------------
52 1 1 10 NULL 0.4752
NULL NULL
52 1 1 20 NULL 0.57599998
NULL NULL
52 2 1 20 NULL 0.57599998
NULL NULL
52 2 2 20 NULL 0.57599998
NULL NULL
52 3 1 20 NULL 0.57599998
NULL NULL
52 3 1 30 NULL 0.1584
NULL NULL
52 3 3 20 NULL 0.29519999
NULL NULL
52 3 3 20 NULL 0.29519999
NULL NULL
52 3 3 50 NULL 0.29519999
NULL NULL
i would like to get result max(timenum) per setid,insertid
in my case it is (ps. i dont want to add identity column)
prjnum setid insertid timenum top_m vb1
vb2 vb3
----------- -------- ----------- ------------------------ ------------------
------ ------------------------ ------------------------
52 1 1 20 NULL 0.57599998
NULL NULL
52 2 1 20 NULL 0.57599998
NULL NULL
52 2 2 20 NULL 0.57599998
NULL NULL
52 3 1 30 NULL 0.1584
NULL NULL
52 3 3 50 NULL 0.29519999
NULL NULL

by **Uri Diman** » Wed, 27 Feb 2002 23:07:54

hello!
this is result from my select query
select * from table WHERE prjnum = 52 and vb1 is not null
prjnum setid insertid timenum top_m vb1
----------- -------- ----------- ------------------------ ------------------
------ ------------------------ ------------------------
52 1 1 10 NULL 0.4752
52 1 1 20 NULL 0.57599998
52 2 1 20 NULL 0.57599998
52 2 2 20 NULL 0.57599998
52 3 1 20 NULL 0.57599998
52 3 1 30 NULL 0.1584
52 3 3 20 NULL 0.29519999
52 3 3 20 NULL 0.29519999
52 3 3 50 NULL 0.29519999

i would like to get result max(timenum) per setid,insertid
in my case it is (ps. i dont want to add identity column)
prjnum setid insertid timenum top_m

vb1---------- -------- ----------- ------------------------ ----------------
--
52 1 1 20 NULL 0.57599998
52 2 1 20 NULL 0.57599998
52 2 2 20 NULL 0.57599998
52 3 1 30 NULL 0.1584
52 3 3 50 NULL 0.29519999

by **praveen maddal** » Wed, 27 Feb 2002 23:20:06

Try this,

select * from table A WHERE A.prjnum = 52 and A.vb1 is not null and
A.timenum
= (select max(timenum) as timenum from table B where A.setid = b.setid
and A.insertid = b.insertid and B.prjnum = 52 and B.vb1 is not null )

Praveen Maddali
MCDBA

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

by **Gui-Dong Xiao [M** » Fri, 01 Mar 2002 16:07:25

Hi Uri,

You can use the following statement:

select T1.* from table as T1
where T1.timenum=(select max(timenum) from table as T2 where
T1.setid=T2.setid and T1.insertid=T2.insertid)
and T1.prjnum = 52 and T1.vb1 is not null
order by T1.setid,T1.insertid

This posting is provided "AS IS" with no warranties, and confers no rights.

Best Regards,
Gui-Dong Xiao
Microsoft Support

Very Computer

select question

select question

select question

select question

select question