Select Max record !Help!

Question

Select Max record !Help!

bmanning 70526

SSC Rookie

Points: 33
More actions
March 4, 2019 at 12:18 pm

#381624

Hello
I am trying to select the Max record between irt_Serial 95365 and 95366. The criteria is to select max app_num when the year and the quarter are the same. Also, both records have the same lic_serial. I want to select all the records records below except irt_Serial 95365. Below is my code and datasets. Please let me know if any questions.
-- creates a temp table to hold the highest ifta_return.app_num (by year/quarter)
If OBJECT_ID('tempdb..#tmpmaxgroup1') IS NOT NULL DROP TABLE #tmpmaxgroup1
select year, quarter, lic_serial, fivg_serial, irt_serial, max (app_num) as max_type_app_num
into #tmpmaxgroup1
from ifta_return IFTA
where lic_serial = '25531'
group by year, quarter, lic_serial, fivg_serial, irt_serial
year quarter lic_serial fivg_serial irt_serial max_type_app_num
2005 1 25531 165110 84523 1
2005 2 25531 175090 89760 1
2005 3 25531 185787 95365 1
2005 3 25531 185790 95366 2
2005 4 25531 196653 101069 1

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Michael L John One Orange Chip Points: 27079 More actions · Answer 1

bmanning 70526 - Monday, March 4, 2019 12:18 PM
Hello
I am trying to select the Max record between irt_Serial 95365 and 95366. The criteria is to select max app_num when the year and the quarter are the same. Also, both records have the same lic_serial. I want to select all the records records below except irt_Serial 95365. Below is my code and datasets. Please let me know if any questions.
-- creates a temp table to hold the highest ifta_return.app_num (by year/quarter)
If OBJECT_ID('tempdb..#tmpmaxgroup1') IS NOT NULL DROP TABLE #tmpmaxgroup1
select year, quarter, lic_serial, fivg_serial, irt_serial, max (app_num) as max_type_app_num
into #tmpmaxgroup1
from ifta_return IFTA
where lic_serial = '25531'
group by year, quarter, lic_serial, fivg_serial, irt_serial
year quarter lic_serial fivg_serial irt_serial max_type_app_num
2005 1 25531 165110 84523 1
2005 2 25531 175090 89760 1
2005 3 25531 185787 95365 1
2005 3 25531 185790 95366 2
2005 4 25531 196653 101069 1

You have not provided the schema or sample data for the table ifta_return, so providing something that will work may not be possible.

However, assuming that the ifta_return table contains all the columns in your query, it looks as if your query will give you exactly what you are looking for.
What is failing for you? And why do you need to use a temp table?

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

bmanning 70526 SSC Rookie Points: 33 More actions · Answer 2

Michael L John - Monday, March 4, 2019 12:41 PM
bmanning 70526 - Monday, March 4, 2019 12:18 PM
Hello
I am trying to select the Max record between irt_Serial 95365 and 95366. The criteria is to select max app_num when the year and the quarter are the same. Also, both records have the same lic_serial. I want to select all the records records below except irt_Serial 95365. Below is my code and datasets. Please let me know if any questions.
-- creates a temp table to hold the highest ifta_return.app_num (by year/quarter)
If OBJECT_ID('tempdb..#tmpmaxgroup1') IS NOT NULL DROP TABLE #tmpmaxgroup1
select year, quarter, lic_serial, fivg_serial, irt_serial, max (app_num) as max_type_app_num
into #tmpmaxgroup1
from ifta_return IFTA
where lic_serial = '25531'
group by year, quarter, lic_serial, fivg_serial, irt_serial
year quarter lic_serial fivg_serial irt_serial max_type_app_num
2005 1 25531 165110 84523 1
2005 2 25531 175090 89760 1
2005 3 25531 185787 95365 1
2005 3 25531 185790 95366 2
2005 4 25531 196653 101069 1
You have not provided the schema or sample data for the table ifta_return, so providing something that will work may not be possible.
However, assuming that the ifta_return table contains all the columns in your query, it looks as if your query will give you exactly what you are looking for.
What is failing for you? And why do you need to use a temp table?

Hello Michael,

The field names you see are from the ifta_return table. We use temp tables because we are using the code to validate record counts. this does not affect the problem. The query looks like it works but it does not. Below is what I am looking for.

Currently:

year	quarter	lic_serial	fivg_serial	irt_serial	max_type_app_num
2005	1	25531	165110	84523	1
2005	2	25531	175090	89760	1
2005	3	25531	185787	95365	1
2005	3	25531	185790	95366	2
2005	4	25531	196653	101069	1

What I want to see:

year	quarter	lic_serial	fivg_serial	irt_serial	max_type_app_num
2005	1	25531	165110	84523	1
2005	2	25531	175090	89760	1

2005	3	25531	185790	95366	2
2005	4	25531	196653	101069	1

Michael L John One Orange Chip Points: 27079 More actions · Answer 3

bmanning 70526 - Monday, March 4, 2019 12:51 PM
Michael L John - Monday, March 4, 2019 12:41 PM
bmanning 70526 - Monday, March 4, 2019 12:18 PM
Hello
I am trying to select the Max record between irt_Serial 95365 and 95366. The criteria is to select max app_num when the year and the quarter are the same. Also, both records have the same lic_serial. I want to select all the records records below except irt_Serial 95365. Below is my code and datasets. Please let me know if any questions.
-- creates a temp table to hold the highest ifta_return.app_num (by year/quarter)
If OBJECT_ID('tempdb..#tmpmaxgroup1') IS NOT NULL DROP TABLE #tmpmaxgroup1
select year, quarter, lic_serial, fivg_serial, irt_serial, max (app_num) as max_type_app_num
into #tmpmaxgroup1
from ifta_return IFTA
where lic_serial = '25531'
group by year, quarter, lic_serial, fivg_serial, irt_serial
year quarter lic_serial fivg_serial irt_serial max_type_app_num
2005 1 25531 165110 84523 1
2005 2 25531 175090 89760 1
2005 3 25531 185787 95365 1
2005 3 25531 185790 95366 2
2005 4 25531 196653 101069 1
You have not provided the schema or sample data for the table ifta_return, so providing something that will work may not be possible.
However, assuming that the ifta_return table contains all the columns in your query, it looks as if your query will give you exactly what you are looking for.
What is failing for you? And why do you need to use a temp table?
Hello Michael,
The field names you see are from the ifta_return table. We use temp tables because we are using the code to validate record counts. this does not affect the problem. The query looks like it works but it does not. Below is what I am looking for.
Currently:
year quarter lic_serial fivg_serial irt_serial max_type_app_num
2005 1 25531 165110 84523 1
2005 2 25531 175090 89760 1
2005 3 25531 185787 95365 1
2005 3 25531 185790 95366 2
2005 4 25531 196653 101069 1
What I want to see:
year quarter lic_serial fivg_serial irt_serial max_type_app_num
2005 1 25531 165110 84523 1
2005 2 25531 175090 89760 1
2005 3 25531 185790 95366 2
2005 4 25531 196653 101069 1

The link in my signature provides instructions on how to post on a forum.

It certainly appears that your query will provide the answer you are looking for. You have given us a sample of what you want, but the WRONG results you are getting would help also.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

drew.allen SSC Guru Points: 77014 More actions · Answer 4

There are two issues here. The first one is identifying the correct group. Your English description doesn't match the code. Your English description says that the records should have the same year, quarter, and lic_serial. THAT is your group. You are getting extra record(s) because your code uses more fields to define the group, so you are getting more records.

The second thing is you need to decided whether you want the remaining values to be correlated or independent. That is, must all values come from the same record (correlated) or could they come from different records (independent). To get correlated values, you need to use a CTE/ROW_NUMBER(). GROUP BY will give you independent values. I suspect that you started off with the right group, but kept adding fields, because you wanted correlated values, but were getting independent values.

Here is the CTE/ROW_NUMBER() version.
-- creates a temp table to hold the highest ifta_return.app_num (by year/quarter) If OBJECT_ID('tempdb..#tmpmaxgroup1') IS NOT NULL DROP TABLE #tmpmaxgroup1;

WITH CTE AS
(
select year, quarter, lic_serial, fivg_serial, irt_serial, app_num, ROW_NUMBER() OVER(PARTITION BY year, quarter, lic_serial ORDER BY irt_serial DESC, app_num DESC) AS rn
into #tmpmaxgroup1
from ifta_return IFTA
where lic_serial = '25531'
group by year, quarter, lic_serial, fivg_serial, irt_serial
)
SELECT year, quarter, lic_serial, fivg_serial, irt_serial, app_num
FROM CTE
WHERE rn = 1;

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Jonathan AC Roberts SSCoach Points: 18407 More actions · Answer 5

drew.allen - Monday, March 4, 2019 1:04 PM
There are two issues here. The first one is identifying the correct group. Your English description doesn't match the code. Your English description says that the records should have the same year, quarter, and lic_serial. THAT is your group. You are getting extra record(s) because your code uses more fields to define the group, so you are getting more records.
The second thing is you need to decided whether you want the remaining values to be correlated or independent. That is, must all values come from the same record (correlated) or could they come from different records (independent). To get correlated values, you need to use a CTE/ROW_NUMBER(). GROUP BY will give you independent values. I suspect that you started off with the right group, but kept adding fields, because you wanted correlated values, but were getting independent values.
Here is the CTE/ROW_NUMBER() version.
-- creates a temp table to hold the highest ifta_return.app_num (by year/quarter) If OBJECT_ID('tempdb..#tmpmaxgroup1') IS NOT NULL DROP TABLE #tmpmaxgroup1;
WITH CTE AS
(
select year, quarter, lic_serial, fivg_serial, irt_serial, app_num, ROW_NUMBER() OVER(PARTITION BY year, quarter, lic_serial ORDER BY irt_serial DESC, app_num DESC) AS rn
into #tmpmaxgroup1
from ifta_return IFTA
where lic_serial = '25531'
group by year, quarter, lic_serial, fivg_serial, irt_serial
)
SELECT year, quarter, lic_serial, fivg_serial, irt_serial, app_num
FROM CTE
WHERE rn = 1;
Drew

Shouldn't "INTO #tmpmaxgroup1" be just above the "FROM CTE"?

Jonathan AC Roberts SSCoach Points: 18407 More actions · Answer 6

I can think of two ways to get the results you want:
IF OBJECT_ID('tempdb..#tmpmaxgroup1') IS NOT NULL DROP TABLE #tmpmaxgroup1; ;WITH CTE AS ( SELECT year, quarter, lic_serial, fivg_serial, irt_serial, app_num, ROW_NUMBER() OVER(PARTITION BY year, quarter, lic_serial ORDER BY app_num DESC, irt_serial DESC, app_num DESC) AS rn FROM ifta_return IFTA WHERE lic_serial = '25531' GROUP BY year, quarter, lic_serial, fivg_serial, irt_serial ) SELECT year, quarter, lic_serial, fivg_serial, irt_serial, app_num INTO #tmpmaxgroup1 FROM CTE WHERE rn = 1;

IF OBJECT_ID('tempdb..#tmpmaxgroup1') IS NOT NULL DROP TABLE #tmpmaxgroup1; ;WITH CTE AS ( SELECT DISTINCT year, quarter, lic_serial FROM ifta_return IFTA WHERE lic_serial = '25531' ) SELECT x.year, x.quarter, x.lic_serial, a.fivg_serial, a.irt_serial, a.app_num INTO #tmpmaxgroup1 FROM CTE x CROSS APPLY(SELECT TOP(1) a.fivg_serial, a.irt_serial, a.app_num FROM ifta_return a WHERE a.year = x.year AND a.quarter = x.quarter AND a.lic_serial = x.lic_serial ORDER BY a.app_num DESC, a.irt_serial DESC, a.app_num DESC) a;

bmanning 70526 SSC Rookie Points: 33 More actions · Answer 7

Jonathan AC Roberts - Monday, March 4, 2019 2:11 PM
I can think of two ways to get the results you want:
IF OBJECT_ID('tempdb..#tmpmaxgroup1') IS NOT NULL DROP TABLE #tmpmaxgroup1; ;WITH CTE AS ( SELECT year, quarter, lic_serial, fivg_serial, irt_serial, app_num, ROW_NUMBER() OVER(PARTITION BY year, quarter, lic_serial ORDER BY app_num DESC, irt_serial DESC, app_num DESC) AS rn FROM ifta_return IFTA WHERE lic_serial = '25531' GROUP BY year, quarter, lic_serial, fivg_serial, irt_serial ) SELECT year, quarter, lic_serial, fivg_serial, irt_serial, app_num INTO #tmpmaxgroup1 FROM CTE WHERE rn = 1;
IF OBJECT_ID('tempdb..#tmpmaxgroup1') IS NOT NULL DROP TABLE #tmpmaxgroup1; ;WITH CTE AS ( SELECT DISTINCT year, quarter, lic_serial FROM ifta_return IFTA WHERE lic_serial = '25531' ) SELECT x.year, x.quarter, x.lic_serial, a.fivg_serial, a.irt_serial, a.app_num INTO #tmpmaxgroup1 FROM CTE x CROSS APPLY(SELECT TOP(1) a.fivg_serial, a.irt_serial, a.app_num FROM ifta_return a WHERE a.year = x.year AND a.quarter = x.quarter AND a.lic_serial = x.lic_serial ORDER BY a.app_num DESC, a.irt_serial DESC, a.app_num DESC) a;

Hello Jonathan,

You are right on the money. Thanks for all your help. You read the instructions and figured it out. I hope you have a great day.

Brian