Menu
Trying to get the last and unique record from this table below. There are some doubles in it, those should be filtered out.
id topic action date
1 10127 2 2015-09-24 15:28:30
2 10127 4 2015-09-24 15:29:26
3 10127 2 2015-09-24 15:30:01
4 10127 3 2015-09-24 15:30:55
5 10127 1 2015-09-24 16:07:25
6 10127 5 2015-09-24 16:10:25
7 10127 4 2015-09-24 16:29:26
Using this query (found here) is my best effort but returns only one result.
SELECT MAX(action) as action,topic,date FROM ......
GROUP by topic
ORDER by action DESC
Would like to get this listing as latest entry and unique on 'action' for 'topic':
id topic action date
3 10127 1 2015-09-24 15:30:01
4 10127 2 2015-09-24 15:30:55
5 10127 3 2015-09-24 16:07:25
6 10127 4 2015-09-24 16:10:25
7 10127 5 2015-09-24 16:29:26
Hope someone has a solution! Thanks!
942
You can use the LOOKUP Function to find the last non-empty cell in a column. Let's walk through this formula. The part of the formula B:B<>”” returns an array containing True and False values: {FALSE, TRUE, TRUE,…}, testing each cell in column B is blank (FALSE).
If we wish to get only the first column number, we can use the MIN function to extract just the first column number, which will be the lowest number in the array. Once we get the first column, we can just add the total columns in the range and subtract 1, to get the last column number.
(1.) Click the column name that you want to transpose data based on, and select Primary Key; (2.) Click another column that you want to transpose, and click Combine then choose one separator to separate the combined data, such as space, comma, semicolon.
You can do it with a subquery. This is the full sqlfiddle: http://sqlfiddle.com/#!9/f7afa/23
Select * FROM (
SELECT
DISTINCT `topic`, `action`, `date`
FROM
ForgeRock
ORDER by date DESC, action ASC
) as X
GROUP BY action
You need to use a sub-query:
SELECT *
FROM yourtable
JOIN (
SELECT topic, MAX(action)
FROM yourtable
GROUP BY topic
) AS child ON (yourtable.topic = topic) AND (yourtable.action = child.action)
The subquery finds the largest action for every topic. That data is then used to join against the same table, which you use to fetch the other fields in that "max'd" record.
43
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
