andyw27
asked on
SQL MAX Help
Hi,
I have a very simple select statement that returns this dataset
SELECT parent_id, component_id, control_id FROM item where parent_id = '14923'
parent_id component_id control_id
14923 12054 53715
14923 12054 53721
14923 12054 53715
14923 12054 53721
14923 12423 53715
14923 12423 53721
14923 14176 53715
14923 14176 53721
14923 14783 53715
14923 14783 53721
14923 14795 53715
14923 14795 53721
14923 14840 53715
14923 14840 53721
14923 14880 53715
14923 14880 53721
14923 14924 53715
14923 14924 53721
14923 14926 53715
14923 14926 53721
I need to add an additional where clause that only returns the highest control values, so in this case the returned dataset would look like this:
parent_id component_id control_id
14923 12054 53721
14923 12054 53721
14923 12423 53721
14923 14176 53721
14923 14783 53721
14923 14795 53721
14923 14840 53721
14923 14880 53721
14923 14924 53721
14923 14926 53721
Any suggestions as to how I can accomplish this please
I have a very simple select statement that returns this dataset
SELECT parent_id, component_id, control_id FROM item where parent_id = '14923'
parent_id component_id control_id
14923 12054 53715
14923 12054 53721
14923 12054 53715
14923 12054 53721
14923 12423 53715
14923 12423 53721
14923 14176 53715
14923 14176 53721
14923 14783 53715
14923 14783 53721
14923 14795 53715
14923 14795 53721
14923 14840 53715
14923 14840 53721
14923 14880 53715
14923 14880 53721
14923 14924 53715
14923 14924 53721
14923 14926 53715
14923 14926 53721
I need to add an additional where clause that only returns the highest control values, so in this case the returned dataset would look like this:
parent_id component_id control_id
14923 12054 53721
14923 12054 53721
14923 12423 53721
14923 14176 53721
14923 14783 53721
14923 14795 53721
14923 14840 53721
14923 14880 53721
14923 14924 53721
14923 14926 53721
Any suggestions as to how I can accomplish this please
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Use the MAX() function:
SELECT parent_id, component_id, MAX(control_id)
FROM item
WHERE parent_id = '14923'
GROUP BY parent_id, component_id
The above answer is correct.
If you want to eliminate duplicates, use DISTINCT.
If you want to eliminate duplicates, use DISTINCT.
SELECT DISTINCT parent_id, component_id, MAX(control_id)
FROM item
WHERE parent_id = '14923'
GROUP BY parent_id, component_id
Also, if you'd like further reading on aggregates such as MAX then I have an article out there called SQL Server GROUP BY Solutions that would be an excellent reading.
Jim, you won't need the DISTINCT since it's already aggregate by parent_id and component_id so duplicates will be automatically dropped.
ASKER
Thanks, worked like a charm.
Andy, any of these solutions works (Vikas forgot the WHERE clause) but there's no need to use subselects.
You can do it in a more direct and efficient way.
Cheers
You can do it in a more direct and efficient way.
Cheers
Glad I could help!
@Vitor and Jim: on your queries, if a component_id does not have a row where control_id is 53721, it will be listed with the highest control_id value if finds. The OP wanted only rows where control_id is 53721. At least that's the way I understood the question.
@Vitor and Jim: on your queries, if a component_id does not have a row where control_id is 53721, it will be listed with the highest control_id value if finds. The OP wanted only rows where control_id is 53721. At least that's the way I understood the question.
Dan, I didn't understand the question like that:
where clause that only returns the highest control values
Hello,
SELECT parent_id, component_id, control_id FROM item a
INNER JOIN
(Select Parent_id, Component_id, (select max(control_id) from item) ControlID from item) B
ON A.parent_id = B.Parent_id AND A.component_id = B.component_id AND A.control_id = B.control_id
SELECT parent_id, component_id, control_id FROM item a
INNER JOIN
(Select Parent_id, Component_id, (select max(control_id) from item) ControlID from item) B
ON A.parent_id = B.Parent_id AND A.component_id = B.component_id AND A.control_id = B.control_id
Vitor - You are correct. What was I thinking...
Open in new window