Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Select only rows with Max Value on a column with multiple tables


Select only rows with Max Value on a column with multiple tables

Author
Message
tubbscc
tubbscc
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
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.



Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16564 Visits: 17014
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)
Suresh Babu Palla
Suresh Babu Palla
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 453
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.
tubbscc
tubbscc
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
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



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search