SQL MAX Function (Easy one) !!

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

SQL MAX Function (Easy one) !!

Author

Topic

funk.phenomena
Posting Yak Master

121 Posts

Posted - 2011-06-10 : 17:54:51

I have the following Table:

REQUEST ID STEP SYS_CREATION_DATE
123456 1 JAN 01 2011
123456 2 JAN 15 2011
123456 3 JAN 16 2011
201664 1 FEB 01 2011
201664 2 FEB 04 2011
201664 3 FEB 08 2011
201664 4 FEB 12 2011

I need to write a query that outputs just row with the highest "Step" so that the result set appears like so:

REQUEST ID STEP UPDATEDATE
123456 3 JAN 16 2011
201664 4 FEB 12 2011

The following code still shows the duplicate rows. What am I doing wrong:

SELECT REQUEST_ID, MAX(STEP), SYS_CREATION_DATE
FROM DBO.SOURCEDATA
GROUP BY REQUEST_ID, SYS_CREATION_DATE

thanks!!!!!!!!!!!!!!!!!!!!!!!!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-10 : 18:01:31

If you are on SQL 2005 or higher, you can use the row_number function to do this:


with a as
(
   select
      *,row_number() over (partition by request_id order by step desc) as N
   from
      DBO.SOURCEDATA
)
select
   REQUEST_ID,STEP, SYS_CREATION_DATE
from
   a
where
   N = 1;

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-06-10 : 22:19:17

>> I have the following Table: <<

People cannot read your mind, so post your code and clear specs if you really want help. Please post real DDL and not narrative or your own personal programming language. Learn to use ISO-11179 rules for the data element names, avoid needless dialect and use ISO-8601 temporal formats, codes and so forth. Please tell us what SQL product and release you are using. Tell us if you can change the DDL or if you are stuck with it.

Let me do your job for you:

CREATE TABLE Requests
(request_nbr CHAR(6) NOT NULL,
request_step INTEGER NOT NULL,
PRIMARY KEY ((request_nbr, request_step),
request_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL);

>> I need to write a query that outputs just row with the highest "Step" so that the result set appears like so: <<

SELECT X.request_nbr, X.request_step, X.request_date
FROM (SELECT request_nbr, request_step, request_date,
MAX(request_step)
OVER (PARTITION BY request_nbr) AS last_step
FROM Requests) AS X
WHERE X.last_step = X.request_step;

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-06-12 : 04:31:21

quote:
Originally posted by funk.phenomena

I have the following Table:

REQUEST ID STEP SYS_CREATION_DATE
123456 1 JAN 01 2011
123456 2 JAN 15 2011
123456 3 JAN 16 2011
201664 1 FEB 01 2011
201664 2 FEB 04 2011
201664 3 FEB 08 2011
201664 4 FEB 12 2011

I need to write a query that outputs just row with the highest "Step" so that the result set appears like so:

REQUEST ID STEP UPDATEDATE
123456 3 JAN 16 2011
201664 4 FEB 12 2011

The following code still shows the duplicate rows. What am I doing wrong:
SELECT d.*
FROM DBO.SOURCEDATA d
INNER JOIN
(
SELECT REQUEST_ID, MAX(STEP) AS MaxStep~~, SYS_CREATION_DATE~~
FROM DBO.SOURCEDATA
GROUP BY REQUEST_ID~~, SYS_CREATION_DATE~~
)t
ON t.REQUEST_ID = d.REQUEST_ID
AND t.MaxStep = d.STEP

thanks!!!!!!!!!!!!!!!!!!!!!!!!

just change you query like above

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources