I had a list of items in a database with a date against them of when they were last updated. Each item appeared in the database twice and potentially with different dates against them:
When using a “Select Distinct” query this resulted in 2 rows being returned for some of the items because the dates were different.
It didn’t matter which date was displayed in the table i was presenting that was trivial what i needed was the main item to appear just once in the table.
SQL “Group By” was the answer to only display one row and to pick one of the dates to display next to it.
Fundamentally a SQL Group By Statement is as follows:
Select col1, col2, col3 from mytable group by col1, col2, col3
In my case i wanted to group by all columns except 1. The way to do this is to apply an aggregate to each column that should not be grouped i.e. you want to combine them in some way or select just one.
Select col1, col2, max(col3) as col3_name from mytable group by col1, col2
The above query will group col1 and col2 where they both match and display the maximum value from col3 from both rows in its output.
The following are the different aggregrates available (it depends on the SQL Data Type as to which can/should be used) :
COUNT() – Returns the number of rows
FIRST() – Returns the first value
LAST() – Returns the last value
MAX() – Returns the largest value
MIN() – Returns the smallest value
SUM() – Returns the sum
My Infrastructure Blog 