Max Function - 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 2008 Forums

Transact-SQL (2008)

Max Function

Author

Topic

hutty
Starting Member

37 Posts

Posted - 2012-07-17 : 14:03:59

Hi, I am having a hard time getting MAX() to work. In my table I have

ID Name Center Acct Pay Period SeqNo
1 John 101 325 50.00 1 1
1 John 101 325 25.00 1 2
1 John 101 325 30.00 1 3
1 John 101 330 40.00 1 1
1 John 101 330 25.00 1 2
1 John 102 325 50.00 1 1
1 John 102 325 25.00 1 2
1 John 102 325 30.00 1 3
1 John 102 330 40.00 1 1
1 John 102 330 25.00 1 2
1 John 101 325 50.00 2 1
1 John 101 325 25.00 2 2
1 John 101 325 30.00 2 3
1 John 101 330 40.00 2 1
1 John 101 330 25.00 2 2

The results should be, which is the highest seqNo or each center/acct combination:
ID Name Center Acct Pay Period SeqNo
1 John 101 325 30.00 1 3
1 John 101 330 25.00 1 2
1 John 102 325 30.00 1 3
1 John 102 330 25.00 1 2
1 John 101 325 30.00 2 3
1 John 101 330 25.00 2 2

This is what I have thus far.
SELECT DISTINCT TOP (100) PERCENT F2 AS TUID, F3 AS Name, MAX(F5) AS FOP, MAX(F6) AS ACCT, F15 AS SeqNumber, MAX(F17) AS Sequence
FROM Pay
GROUP BY F2, F3, F5, F6, F15, F17

thanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-17 : 14:13:14

Use row number function

SELECT
	ID, NAME, Center, Act, Pay, Period, SeqNo
FROM
(
SELECT
	*,
	ROW_NUMBER() OVER (PARTITION BY Center,Acct ORDER BY seqNo DESC) AS RN
FROM
	Pay
) s
WHERE
   RN = 1

hutty
Starting Member

37 Posts

Posted - 2012-07-17 : 14:41:19

Thanks for the reply sun. I failed to mention that "ID" is not the row ID, but the employee ID number.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-17 : 15:24:29

Whether ID is row number or not should not matter. The key thing to pay attention to is the PARTITION and ORDER by clause in the inner query.

PARTITION BY Center,Acct ORDER BY seqNo DESC

Any columns that you want to consider as a group and pick one row where all those columns have the same value should be in the partition by clause. The order by clause determines which row will be picked.

hutty
Starting Member

37 Posts

Posted - 2012-07-17 : 15:42:00

Sunitabeck, when applying your post the VS debugger comes up and sql ultimately crashes. This is what I have. What's the role of "DESC"?

SELECT
SELECT ID, NAME, CENTER, ACCT, PAYROLL_NUMBER, EVENT_SEQUENCE_NUMBER
FROM
(
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY CENTER,ACCT ORDER BY EVENT_SEQUENCE_NUMBER DESC) AS RN
FROM
PAY) s
WHERE
RN = 1

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-17 : 15:44:16

DESC is for descending (sort order for the order by clause).

Are you using Microsoft SQL Server 2008? Where are you running the query from - a client program or from SQL Server Management Studio?

hutty
Starting Member

37 Posts

Posted - 2012-07-17 : 15:56:22

I am using sql 2005 and sql management studio. thanks

hutty
Starting Member

37 Posts

Posted - 2012-07-17 : 16:12:49

I remember having a problem before when taking sql 2008 query and running it on 2005 and it crashed. I copied the table to 2008 and I am getting incorrect syntax near keyword Select.

hutty
Starting Member

37 Posts

Posted - 2012-07-17 : 16:16:16

Got it to work. Checking the results

hutty
Starting Member

37 Posts

Posted - 2012-07-17 : 16:23:24

It didn't work. I think because the pay field is different and it is treating that line a separate filter. The SeqNo is the last entry against the ID,Center,Acct in a particular period. Some employees are paid on various combination of Centers and Accts, which vary from period to period.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-17 : 17:58:24

quote:
Originally posted by hutty

It didn't work. I think because the pay field is different and it is treating that line a separate filter. The SeqNo is the last entry against the ID,Center,Acct in a particular period. Some employees are paid on various combination of Centers and Accts, which vary from period to period.

The original query that I posted was based on your description that you want to pick the "highest seqNo or each center/acct combination". If there are other rules, if you can outline those, we can modify the query.

Since I (or anyone else on the forum) can work with only sample data and your description of the problem, usually what people recommend is that you post DDL's for tables along with REPRESENTATIVE data.

hutty
Starting Member

37 Posts

Posted - 2012-07-17 : 18:05:40

Sunitabeck,
You are correct. I was able to get the query to work the way I described it initially by adding another field to the nested select statement. After all that I found out that I needed all the max Seq_No for each Period, which could be more than one. If 12 is the highest, I need all rows with 12 for that period. I think I am pretty close to it.

Thanks for your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources