June 20, 2013 at 3:04 pm
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 PRODUCTWater % 1
2 1967 SULFUR DIOXIDE Sulfur Dioxide 1
3 1967 PROPIONITIRLE Propionitrile ppm1
4 1967 ACRYONITRILE Acrylonitrile ppm1
5 1967 ACETONITRILE Acetonitrile ppm1
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 PACKEDPK CLR Acidity 2
12 1967 %ACIDITY IN PACKEDPK 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.
June 20, 2013 at 3:29 pm
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 20, 2013 at 5:02 pm
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.
June 20, 2013 at 5:37 pm
Thanks Sean, that did it.
I feel better since I was pretty close overall and I have a better understanding now
Thanks again
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply