Join two Select on one table with related result

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.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Join two Select on one table with related result

Author

Topic

behnam_20
Starting Member

2 Posts

Posted - 2010-08-22 : 14:05:50

I've got one table like this:
id file_id doc_no revision

id is unique, each file (file_id) may have some document and each document some revision. (doc_no represent document number and revision is revision number)
Here is three select for getting last doc_no and revision in a file and its id

Q1: SET @docNum= (SELECT MAX(doc_no) FROM myTable
WHERE file_id=@file_id)

(ie return 4)

Q2: SET @rev=(SELECT MAX(revision) FROM myTable WHERE doc_number=@docNum AND file_id=@file_id)

(ie return 2)

Q3: SET @Id=(SELECT id FROM myTable WHERE doc_number=@docNum AND revision=@rev AND file_id=@file_id)

Can I join Q1,Q2 together (in one select query) and get one result set like the following:
doc_num rev_num
4 2

Can I join Q1,Q2,Q3 in one query to get file_id?

Please world, stop using IE6

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-08-22 : 16:47:07

Try this:


SELECT TOP(1) file_id, doc_no, revision
  FROM (SELECT TOP(1) WITH TIES file_id, doc_no, revision
          FROM YourTable
         WHERE file_id = 1
         ORDER BY doc_no DESC) AS T
 ORDER BY revision DESC

behnam_20
Starting Member

2 Posts

Posted - 2010-08-23 : 03:27:11

Thanks, this work

Please world, stop using IE6

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources