Select the maximum value - 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 2005 Forums

Transact-SQL (2005)

Select the maximum value

Author

Topic

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2009-07-14 : 05:50:43

Hallo,

I have the following query which works fine. This select query extractes 3 records. What I want to do is extract ONLY the record with the maximum col_uid. Please could someone help me to update the query..

Thanks

SELECT     TOP (100) PERCENT dbo.QryColProcessing.prac_no, MAX(dbo.QryColProcessing.col_uid) AS col_uid, MAX(dbo.QryColProcessing.audit_end) 
                      AS audit_end, gprdsql.TblColProcessing.status, gprdsql.TblColProcessing.stage, MAX(dbo.QryColProcessing.audit_end + 1) AS audit_start
FROM         gprdsql.TblColProcessing INNER JOIN
                      dbo.QryColProcessing ON gprdsql.TblColProcessing.col_uid = dbo.QryColProcessing.col_uid
GROUP BY dbo.QryColProcessing.prac_no, gprdsql.TblColProcessing.status, gprdsql.TblColProcessing.stage
HAVING      (gprdsql.TblColProcessing.status = 'completed') AND (gprdsql.TblColProcessing.stage = 'cancelled') OR
                      (gprdsql.TblColProcessing.status = 'completed') AND (gprdsql.TblColProcessing.stage = 'stage 8')
ORDER BY dbo.QryColProcessing.prac_no

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-14 : 06:07:19

Make your query a drived table "(select ...) as dt" and make a "select top 1 * from dt order by col_uid DESC"

No, you're never too old to Yak'n'Roll if you're too young to die.

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2009-07-14 : 06:49:24

please elaborate it is not clear

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-14 : 07:00:57

[code]
select top 1 * from
(
SELECT TOP (100) PERCENT dbo.QryColProcessing.prac_no, MAX(dbo.QryColProcessing.col_uid) AS col_uid, MAX(dbo.QryColProcessing.audit_end)
AS audit_end, gprdsql.TblColProcessing.status, gprdsql.TblColProcessing.stage, MAX(dbo.QryColProcessing.audit_end + 1) AS audit_start
FROM gprdsql.TblColProcessing INNER JOIN
dbo.QryColProcessing ON gprdsql.TblColProcessing.col_uid = dbo.QryColProcessing.col_uid
GROUP BY dbo.QryColProcessing.prac_no, gprdsql.TblColProcessing.status, gprdsql.TblColProcessing.stage
HAVING (gprdsql.TblColProcessing.status = 'completed') AND (gprdsql.TblColProcessing.stage = 'cancelled') OR
(gprdsql.TblColProcessing.status = 'completed') AND (gprdsql.TblColProcessing.stage = 'stage 8')
ORDER BY dbo.QryColProcessing.prac_no
) as dt
order by col_uid DESC
[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-07-14 : 07:05:33

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/find-nth-maximum-value.aspx

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2009-07-14 : 07:51:47

Thanks

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2009-07-14 : 08:22:15

webfred the code works fine!! However, I have a minor problem the initial result was as shown below

prac_no col_uid audit_end audit_start
12 45 345 4373
12 56 635 7373
16 57 774 9383
20 23 848 8333

After having your code the result was

prac_no col_uid audit_end audit_start
16 57 774 9383

What I want to have as my final result is as shown below, note taking only the maximum col_uid between the 2 records

prac_no col_uid audit_end audit_start

12 56 635 7373
16 57 774 9383
20 23 848 8333

Thanks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-14 : 08:23:57

What is the output of your original select?
Is there data missing?

No, you're never too old to Yak'n'Roll if you're too young to die.

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2009-07-14 : 08:32:03

Please not the changes....

Thanks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-14 : 08:34:46

So you can change "TOP 1" by "TOP 3" to get 3 records and don't forget the order by as to see in my code!
And for speeding up this query you should delete this:
ORDER BY dbo.QryColProcessing.prac_no

Fred

No, you're never too old to Yak'n'Roll if you're too young to die.

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2009-07-14 : 08:44:07

Thanks Fred...

But the number of records are un-predictable. This time its 3 fine. But next time it might be 5 or 10 or 1000. How can I accomodate this!!

Also, I want ONLY to check the col_uid between the 2 records with the same prac_no and select the one with maximum col_uid and display with the rest.

Therefore, whenever there is more than one prac_no the one with maximum col_uid will be included in the result.

I hope am clear..

Many thanks

Thank you

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-14 : 09:02:37

Ok now I know what you want.
I'll be back!

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-14 : 09:08:07

[code]select prac_no,col_uid,audit_end,[status],stage,audit_start
from
(
select
row_number() over (partition by prac_no order by col_uid desc) as rownum,
*
from
(
SELECT TOP (100) PERCENT dbo.QryColProcessing.prac_no, MAX(dbo.QryColProcessing.col_uid) AS col_uid, MAX(dbo.QryColProcessing.audit_end)
AS audit_end, gprdsql.TblColProcessing.status, gprdsql.TblColProcessing.stage, MAX(dbo.QryColProcessing.audit_end + 1) AS audit_start
FROM gprdsql.TblColProcessing INNER JOIN
dbo.QryColProcessing ON gprdsql.TblColProcessing.col_uid = dbo.QryColProcessing.col_uid
GROUP BY dbo.QryColProcessing.prac_no, gprdsql.TblColProcessing.status, gprdsql.TblColProcessing.stage
HAVING (gprdsql.TblColProcessing.status = 'completed') AND (gprdsql.TblColProcessing.stage = 'cancelled') OR
(gprdsql.TblColProcessing.status = 'completed') AND (gprdsql.TblColProcessing.stage = 'stage 8')
) as dt
) as dt2
where rownum = 1[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2009-07-14 : 09:52:50

Thank you very much.. Worked perfect!!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-14 : 09:56:37

welcome