Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Select only rows with Max Value on a column with multiple tables Expand / Collapse
Author
Message
Posted Thursday, June 20, 2013 3:04 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 3, 2013 1:24 PM
Points: 6, Visits: 28
The query returns all the rows needed but you can see the bottom 4 rows are duplicates of each other except for the limit_ver_num field.

We want to return the first 8 rows and the two rows where limit_ver_num = 2. We want to return the row with the largest limit_ver_num if there is a duplicate record.
So the correct results would include rows 1-9 and row 11
Rows 10 and 12 should not be included in the results

My current example is in SQL 2008 but will eventually be used in Oracle if that matters
SELECT sam_typ_tst_typ.SAMPLE_TYPE_NUM, sample_type_name, method_name, method_descr_text, test_type_name, limit_ver_num
FROM sam_typ_tst_typ, SAMPLE_TYPE, method, limits, test_type, area
where
sam_typ_tst_typ.sample_type_num = sample_type.sample_type_num and
sam_typ_tst_typ.TEST_TYPE_NUM = test_type.test_type_num and
sam_typ_tst_typ.METHOD_NUM = METHOD.METHOD_NUM and
sam_typ_tst_typ.SAMPLE_TYPE_NUM = LIMITS.sample_type_num AND
sam_typ_tst_typ.TEST_TYPE_NUM = LIMITS.TEST_TYPE_NUM AND
SAMPLE_TYPE.AREA_NUM = AREA.area_num and
sam_typ_tst_typ.SAMPLE_TYPE_NUM in (select SAMPLE_TYPE_num from SAMPLE_TYPE
WHERE SAMPLE_TYPE_NAME = 'MEHCN_INPROCESS');

The row column here is strictly to help count the rows, it is not an ID column

OUTPUT:
   STN	       method_descr_text        test_type_name	   limit_ver_num
1 1967 % WATER IN HCN PRODUCT Water % 1
2 1967 SULFUR DIOXIDE Sulfur Dioxide 1
3 1967 PROPIONITIRLE Propionitrile ppm 1
4 1967 ACRYONITRILE Acrylonitrile ppm 1
5 1967 ACETONITRILE Acetonitrile ppm 1
6 1967 % Acidity in HCN HCN PR Acidity 1
7 1967 NH3 IN EP78 - HC Ammonia ppm 1
8 1967 % ACIDITY IN HC ST BTM Acidity 1
9 1967 PACKED COOLER PH pH 2
10 1967 PACKED COOLER PH pH 1
11 1967 %ACIDITY IN PACKED PK CLR Acidity 2
12 1967 %ACIDITY IN PACKED PK CLR Acidity 1

I've tried to get this using:

SELECT sam_typ_tst_typ.SAMPLE_TYPE_NUM, sample_type_name, method_name, method_descr_text, test_type_name, MAX(limit_ver_num)
FROM sam_typ_tst_typ, SAMPLE_TYPE, method, limits, test_type, area
where
sam_typ_tst_typ.sample_type_num = sample_type.sample_type_num and
sam_typ_tst_typ.TEST_TYPE_NUM = test_type.test_type_num and
sam_typ_tst_typ.METHOD_NUM = METHOD.METHOD_NUM and
sam_typ_tst_typ.SAMPLE_TYPE_NUM = LIMITS.sample_type_num AND
sam_typ_tst_typ.TEST_TYPE_NUM = LIMITS.TEST_TYPE_NUM AND
SAMPLE_TYPE.AREA_NUM = AREA.area_num and
sam_typ_tst_typ.SAMPLE_TYPE_NUM in (select SAMPLE_TYPE_num from SAMPLE_TYPE
WHERE SAMPLE_TYPE_NAME = 'MEHCN_INPROCESS')
GROUP BY sam_typ_tst_typ.SAMPLE_TYPE_NUM, sample_type_name, method_name, method_descr_text

When I use this query I get this message:
Msg 8120, Level 16, State 1, Line 1
Column 'test_type.test_type_name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Help please, I do some sql work but this is outside anything I've tried to find before. The above may end up working but I don't understand the GROUP BY function well enough to modify it correctly with so many tables involved.



Post #1465957
Posted Thursday, June 20, 2013 3:29 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:38 PM
Points: 13,328, Visits: 12,825
You will have to group by ALL columns not part of an aggregate. You did not include text_type_name in your group by.

Try this.

GROUP BY sam_typ_tst_typ.SAMPLE_TYPE_NUM, sample_type_name, method_name, method_descr_text, test_type_name



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1465967
Posted Thursday, June 20, 2013 5:02 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, December 12, 2014 2:27 AM
Points: 19, Visits: 382
Yes as Sean Said you have to specify all the columns that you are using in the select statement in your Group By Clause expect the aggregate.
Post #1465993
Posted Thursday, June 20, 2013 5:37 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 3, 2013 1:24 PM
Points: 6, Visits: 28
Thanks Sean, that did it.
I feel better since I was pretty close overall and I have a better understanding now

Thanks again



Post #1466006
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse