July 18, 2012 at 7:34 am
I am trying to retrieve all rows with the largest value in a particular column. The largest value could return many rows for a particular users. Here is what I have thus far.
SELECT DISTINCT
ID, NAME, FOP, ACCT, CTNUM, ENDDATE, DEBIT, CREDIT, TRANSACTION_DATE, EXPORTED, CALENDAR_YEAR, FISCAL_YEAR, PAYROLL_IDENTIFIER,
PAYROLL_NUMBER, [EARN-SEQNO], EVENT_SEQUENCE_NUMBER
FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY ID, ACCT, PAYROLL_NUMBER,EVENT_SEQUENCE_NUMBER
ORDER BY EVENT_SEQUENCE_NUMBER DESC) AS RN
FROM PAYROLLYEAREND ) s
WHERE RN = 1 AND ID = '16443' AND PAYROLL_NUMBER ='7'
In the above example, the EVENT_SEQUENCE_NUMBER is populated with values from 0 to 12. Could vary per user and PAYROLL_NUMBER. The query above returns 48 rows. However, all I want are the rows where EVENT_SEQUENCE_NUMBER is equal to the highest, which is in the above example is 12. The result would be 29 rows.
Thanks
July 18, 2012 at 7:59 am
I have several different versions of this type of query in this article[/url]. Take a look. I think you'll find something to help.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 18, 2012 at 8:33 am
Grant..thanks for replying. I took a look at the web page, but it's hard to follow along without any real data in the examples and the results thereof.
July 18, 2012 at 10:02 am
thegoose78 (7/18/2012)
Grant..thanks for replying. I took a look at the web page, but it's hard to follow along without any real data in the examples and the results thereof.
Now take a look at your first post and let us know if, as an outsider who cant see your database/tables/data, can help you with your problem?
Please provide some sample data, table structure, business rules and expected result, like this format:
Data:
IF OBJECT_ID('tempdb..#t') IS NOT NULL
DROP TABLE #t
CREATE TABLE #T
(
ColA INT
,ColB INT
,ColC INT
)
INSERT INTO #T
SELECT 1 , 1, 10
UNION ALL SELECT 1 , 2, 11
UNION ALL SELECT 1 , 3, 12
UNION ALL SELECT 2 , 6, 20
UNION ALL SELECT 2 , 8, 21
UNION ALL SELECT 2 , 7, 22
UNION ALL SELECT 3 , 4, 31
UNION ALL SELECT 3 , 4, 32
UNION ALL SELECT 3 , 3, 33
Business Rlule : Get all the rows that has the MAX of ColB for each value of ColA.
Expected Result:
ColAColBColC
1312
2821
3431
3432
July 18, 2012 at 11:31 am
Hi,
I believe I have the solution. Still verifying, but it appears to work.
Thanks.
SELECT DISTINCT
ID, NAME, FOP
, ACCT, CTNUM, ENDDATE
, DEBIT, CREDIT, TRANSACTION_DATE
, EXPORTED, CALENDAR_YEAR, FISCAL_YEAR
, PAYROLL_IDENTIFIER, PAYROLL_NUMBER, [EARN-SEQNO]
, EVENT_SEQUENCE_NUMBER
FROM PAYROLLYEAREND AS s
WHERE ID = '16443'
AND PAYROLL_NUMBER = '7'
AND EVENT_SEQUENCE_NUMBER = (SELECT Max(z1.EVENT_SEQUENCE_NUMBER)
FROM PAYROLLYEAREND AS z1
WHERE z1.ID = s.ID
AND z1.PAYROLL_NUMBER = s.PAYROLL_NUMBER)
July 18, 2012 at 1:10 pm
thegoose78 (7/18/2012)
Hi,I believe I have the solution. Still verifying, but it appears to work.
Thanks.
SELECT DISTINCT
ID, NAME, FOP
, ACCT, CTNUM, ENDDATE
, DEBIT, CREDIT, TRANSACTION_DATE
, EXPORTED, CALENDAR_YEAR, FISCAL_YEAR
, PAYROLL_IDENTIFIER, PAYROLL_NUMBER, [EARN-SEQNO]
, EVENT_SEQUENCE_NUMBER
FROM PAYROLLYEAREND AS s
WHERE ID = '16443'
AND PAYROLL_NUMBER = '7'
AND EVENT_SEQUENCE_NUMBER = (SELECT Max(z1.EVENT_SEQUENCE_NUMBER)
FROM PAYROLLYEAREND AS z1
WHERE z1.ID = s.ID
AND z1.PAYROLL_NUMBER = s.PAYROLL_NUMBER)
If you would provide us with the DDL for the table, some sample data, and expected results I'm sure we could help you with a solution.
July 18, 2012 at 1:16 pm
For example, using the setup provided by ColdCoffee, this is what I came up with for a solution. I have included all the code ColdCoffee provided.
IF OBJECT_ID('tempdb..#t') IS NOT NULL
DROP TABLE #T;
CREATE TABLE #T
(
ColA INT
,ColB INT
,ColC INT
);
INSERT INTO #T
SELECT 1 , 1, 10
UNION ALL SELECT 1 , 2, 11
UNION ALL SELECT 1 , 3, 12
UNION ALL SELECT 2 , 6, 20
UNION ALL SELECT 2 , 8, 21
UNION ALL SELECT 2 , 7, 22
UNION ALL SELECT 3 , 4, 31
UNION ALL SELECT 3 , 4, 32
UNION ALL SELECT 3 , 3, 33;
WITH Basedata AS (
SELECT
ColA,
ColB,
ColC,
DENSE_RANK() OVER (PARTITION BY ColA ORDER BY ColB desc) AS dr
FROM
#T
)
SELECT
ColA,
ColB,
ColC
FROM
Basedata
WHERE
dr = 1;
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply