SELECT MAX - 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

General SQL Server Forums

New to SQL Server Programming

SELECT MAX

Author

Topic

divan
Posting Yak Master

153 Posts

Posted - 2012-08-08 : 10:53:05

I have a table that among other fields it has a STATUS field and a DATE_TIME field.

There can be several rows with different STATUS values and each row has a DATE_TIME..

Please help me with a SELECT MAX where it will select the highest status value and then select the latest DATE_TIME for that STATUS value.

Thank you in advance

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-08 : 11:02:11

If you are expecting to get one row in your final output, this should be sufficient:

SELECT TOP 1 * FROM YourTable ORDER BY STATUS DESC, DATETIME DESC;

But, if you have multiple categories, for example, you want to get one row for each customer (or some other criteria), use row_number function like this:

SELECT * FROM 
(
	SELECT *,ROW_NUMBER() OVER (PARTITION BY Col1,Col2 ORDER BY STATUS DESC, DATETIME DESC) AS RN
	FROM YourTable 
)s
WHERE RN = 1;

Here Col1, Col2 etc. are your partition criteria - for example, CustomerId.

divan
Posting Yak Master

153 Posts

Posted - 2012-08-08 : 11:22:56

I do not understand your response but here is a sample script

SELECT P.X, P.Y,P.Z,R.STATUS, R.STATUS_3
FROM TABLE P
INNER JOIN TABLE R ON P.NUMBER = R.NUMBER AND P.DATE_TIME = R._DATE_TIME
WHERE P.DATE BETWEEN '1/1/12' AND '7/1/12'

AND P.NUMBER NOT LIKE 'Q%'

AND SELECT MAX R.STATUS AND R.DATE_TIME = (SELECT (DATE_TIME) FROM TABLE R2 WHERE R.NUMBER = R2.NUMBER AND R.STATUS = R2.STATUS)----)

SO i NEED THIS SELECT max CORRECTED

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-08 : 12:28:21

What I meant is this:

SELECT X,Y,Z,STATUS,STATUS_3 FROM
(
	SELECT
		P.X, P.Y, P.Z, R.STATUS, R.STATUS_3,
		ROW_NUMBER() OVER (PARTITION BY p.NUMBER ORDER BY R.STATUS DESC,R.DATE_TIME DESC) AS RN
	FROM
		TABLE1 P
		INNER JOIN TABLE2 R
			ON  P.NUMBER = R.NUMBER AND P.DATE_TIME = R._DATE_TIME
	WHERE
		P.DATE BETWEEN '1/1/12' AND '7/1/12'
		AND P.NUMBER NOT LIKE 'Q%'
)s
WHERE RN=1;

This won't work if you are on SQL 2000.

divan
Posting Yak Master

153 Posts

Posted - 2012-08-08 : 13:46:40

Sunitabeck Thanks a lot this works perfect... I just need to understand what the )s WHERE RN= 1 does.. When I comment out WHERE RN = 1 the number of records extracted is more than when i leave it in there..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-08 : 13:51:34

quote:
Originally posted by divan

Sunitabeck Thanks a lot this works perfect... I just need to understand what the )s WHERE RN= 1 does.. When I comment out WHERE RN = 1 the number of records extracted is more than when i leave it in there..

read about ROW_NUMBER() and its uses
http://msdn.microsoft.com/en-us/library/ms186734.aspx
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/multipurpose-row-number-function.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-08 : 13:54:47

If you run just the inner select, you will see that RN is a row_number column that it is creating. It assigns a number to each row based on the order that is specified and the partitioning. So this

ROW_NUMBER() OVER (PARTITION BY p.NUMBER ORDER BY R.STATUS DESC,R.DATE_TIME DESC) AS RN

does the following:
a) the partition clause considers all the rows that have the same p.NUMBER and considers them as a group.
b) then it assigns row numbers starting at 1 to each group by sorting it based on the ORDER BY clause.

RN=1 in the outer query is simply saying, just get me the ones that came first in that sorting scheme.

http://msdn.microsoft.com/en-us/library/ms186734.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources