Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
nssjari
Starting Member
46 Posts |
 Posted - 2005-11-30 : 08:55:13
|
| I have a view based on few tables okayI have a query that gives me document details of various revisionsNow i also get the latest rev of those if i use a MAX() in my QueryNow a case where i have sayDocu Numb - Revi - Received DateDocument1 - rev0 - 10/2004Document1 - rev1 - 11/2004Document1 - rev2 - 12/2004Document1 - rev3 - 01/2005Document1 - rev4 - 02/2005Document1 - rev5 - 03/2005Document1 - rev6 - 04/2005Document1 - rev7 - 05/2005If i query for latest it gives Document1 - rev7And if i filter for a month of 12/2004If i query for latest it should give Document1 - rev2Now i need to get a filter with latest as rev2, plus i also need as a indication of actual latest as rev 7using a queryHow do i do that ...Coz when i use filter for a month and Max then itdoes not consider rev7 for that filter and skip the recordand if i try to filter only as per month .. i get a wrong infoas latest rev2 which is not a true info as per database records.May i possibly get both ... filtered info as per specific month andalso a rev info as what is the latest for a spec document.Just i need to know is that possible .. in SQl using a queryAny more info needed please mail me onneeraj.jariwala@gmail.comLife is beautiful ... When you smile ... |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2005-11-30 : 09:12:58
|
is this what you want ?select *from doc d1, doc d2where d1.doc_no = d2.doc_noand d1.rec_date = (select max(rec_date) from #doc)and d2.rec_date = '12/2004' -----------------[KH] |
 |
|
|
|
|
|
A logical puzzle answer me, is it possible to get