Menu
Here's what I'm trying to do. Let's say I have this table t:
key_id | id | record_date | other_cols 1 | 18 | 2011-04-03 | x 2 | 18 | 2012-05-19 | y 3 | 18 | 2012-08-09 | z 4 | 19 | 2009-06-01 | a 5 | 19 | 2011-04-03 | b 6 | 19 | 2011-10-25 | c 7 | 19 | 2012-08-09 | d For each id, I want to select the row containing the minimum record_date. So I'd get:
key_id | id | record_date | other_cols 1 | 18 | 2011-04-03 | x 4 | 19 | 2009-06-01 | a The only solutions I've seen to this problem assume that all record_date entries are distinct, but that is not this case in my data. Using a subquery and an inner join with two conditions would give me duplicate rows for some ids, which I don't want:
key_id | id | record_date | other_cols 1 | 18 | 2011-04-03 | x 5 | 19 | 2011-04-03 | b 4 | 19 | 2009-06-01 | a 
763
Well, GROUP BY and DISTINCT have their own use. GROUP BY cannot replace DISTINCT in some situations and DISTINCT cannot take place of GROUP BY. It is as per your choice and situation how you are optimizing both of them and choosing where to use GROUP BY and DISTINCT.
To select data where a field has min value, you can use aggregate function min(). The syntax is as follows. SELECT *FROM yourTableName WHERE yourColumnName=(SELECT MIN(yourColumnName) FROM yourTableName);
To find the minimum value of a column, use the MIN() aggregate function; it takes as its argument the name of the column for which you want to find the minimum value. If you have not specified any other columns in the SELECT clause, the minimum will be calculated for all records in the table.
If you use DISTINCT with multiple columns, the result set won't be grouped as it will with GROUP BY, and you can't use aggregate functions with DISTINCT.
How about something like:
SELECT mt.* FROM MyTable mt INNER JOIN ( SELECT id, MIN(record_date) AS MinDate FROM MyTable GROUP BY id ) t ON mt.id = t.id AND mt.record_date = t.MinDate This gets the minimum date per ID, and then gets the values based on those values. The only time you would have duplicates is if there are duplicate minimum record_dates for the same ID.
168
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
