Help with 'Max' statement? - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Help with 'Max' statement?

Author

Topic

jose1lm
Yak Posting Veteran

70 Posts

Posted - 2004-06-17 : 12:12:58

How can I fix the statement below to not SELECT the max id from all the jobs?

SELECT TOP 100 PERCENT *
FROM dbo.job_doc_attached job_doc_attached_sorted
WHERE (id =
(SELECT MAX(id)
FROM job_doc_attached
WHERE doc_name = job_doc_attached_sorted.doc_name))
ORDER BY id DESC

Sample Table:

id | job_id | doc_name
1 | 150 | ABC
2 | 150 | ABCD
3 | 150 | ABC
4 | 151 | ABC
5 | 151 | ABCDE
6 | 151 | ABCDEF

What I want it to do is select the doc_name with the highest id, but also within the same job_id. I don't want it to select from all the jobs.

What the statement is currently returning:

id | job_id | doc_name
2 | 150 | ABCD
4 | 151 | ABC
5 | 151 | ABCDE
6 | 151 | ABCDEF

This is the way I would like the results to look like:

id | job_id | doc_name
2 | 150 | ABCD
3 | 150 | ABC
4 | 151 | ABC
5 | 151 | ABCDE
6 | 151 | ABCDEF

Thanks for your help!

JLM

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-06-17 : 12:17:27

Try this.

SELECT TOP 100 PERCENT *
FROM dbo.job_doc_attached job_doc_attached_sorted
WHERE (id =
(SELECT MAX(id)
FROM job_doc_attached
WHERE doc_name = job_doc_attached_sorted.doc_name
AND job_id = job_doc_attached_sorted.job_id))
ORDER BY id DESC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources