# Using INNER JOIN and GROUP BY to help filter a Rest Get request **URL:** \ **Category:** REST API\ **Created:** [July 21, 2014, 9:23pm UTC](https://community.dreamfactory.com/t/using-inner-join-and-group-by-to-help-filter-a-rest-get-request/190 "2014-07-21T21:23:58Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![bkromhout](https://yyz1.discourse-cdn.com/flex035/user_avatar/community.dreamfactory.com/bkromhout/32/223_2.png) [@bkromhout](https://community.dreamfactory.com/u/bkromhout)\ **Post date:** [July 21, 2014, 9:23pm UTC](https://community.dreamfactory.com/t/using-inner-join-and-group-by-to-help-filter-a-rest-get-request/190/1 "2014-07-21T21:23:58Z")
Hello, I was wondering if there was any support for MySQL’s GROUP BY logic. The reason I’m asking is that for some of my data, I need to satisfy the following requirements when pulling data from a table (Let’s call it Table A) - Only the records in Table A that have a specific value for a string user\_id (This is easy, I can use filters. **Note that this user\_id is part of our data, a regular string, not part of the dream factory user framework** ) - Those records must be between newer than a specified creation date (I can use the filters for this too I assume, by referencing my “date\_created” column which is of type “timestamp\_on\_create” and telling it to be \>= a datetime) - I only want the last record from each day to be returned, if there are multiple records from a day. The last one is where I run into a problem. I can’t see any way to do this currently. I somewhat assume I could somehow use scripts to discard all but the last record from each day, but I can’t get my head around how I could do so. If I was doing it in raw MySQL syntax, I’d be doing it like this: ``` SELECT records.* FROM records INNER JOIN (SELECT DATE(`date`) as day_of,                    MAX(`date`) AS last_record             FROM records             GROUP BY day_of            ) latest ON records.`date` = latest.last_record WHERE records.user_id = '9076224588' ``` I know that that would work. I just can’t figure out how to translate that to what’s available to me on Dreamfactory. Could I get some help here? If it means I need to go into the system files and somehow add an additional method to the REST Api, I’d be willing to do so, but I don’t know where that code lives. ---
**Author:** ![leehicks](https://yyz1.discourse-cdn.com/flex035/user_avatar/community.dreamfactory.com/leehicks/32/55_2.png) [@leehicks](https://community.dreamfactory.com/u/leehicks)\ **Post date:** [July 22, 2014, 1:37pm UTC](https://community.dreamfactory.com/t/using-inner-join-and-group-by-to-help-filter-a-rest-get-request/190/2 "2014-07-22T13:37:11Z")
If you only want the last one of a particular day, it seems to me that you could use the limit and order by clauses to accomplish that, i.e. adding additional SQL like “LIMIT 1 ORDER BY date DESC” or in the DF API syntax, it would be “&limit=1&order=date%20DESC”. If I am not over-simplifying the issue, that should work. Please let me know and we can go from there. ---
**Author:** ![bkromhout](https://yyz1.discourse-cdn.com/flex035/user_avatar/community.dreamfactory.com/bkromhout/32/223_2.png) [@bkromhout](https://community.dreamfactory.com/u/bkromhout)\ **Post date:** [July 22, 2014, 2:05pm UTC](https://community.dreamfactory.com/t/using-inner-join-and-group-by-to-help-filter-a-rest-get-request/190/3 "2014-07-22T14:05:23Z")
That would work for getting the last record from one day, but I’m looking to get the last record from each and every day. ---
**Author:** ![Jason](https://yyz1.discourse-cdn.com/flex035/user_avatar/community.dreamfactory.com/jason/32/1194_2.png) [@Jason](https://community.dreamfactory.com/u/Jason)\ **Post date:** [July 22, 2014, 3:38pm UTC](https://community.dreamfactory.com/t/using-inner-join-and-group-by-to-help-filter-a-rest-get-request/190/4 "2014-07-22T15:38:11Z")
Is that date field actually a datetime field? Also , support for stored procedures is to be released in the next few days, this seems like a good use case, or we could use a server side script in the interim if you’re open to that? ---
**Author:** ![bkromhout](https://yyz1.discourse-cdn.com/flex035/user_avatar/community.dreamfactory.com/bkromhout/32/223_2.png) [@bkromhout](https://community.dreamfactory.com/u/bkromhout)\ **Post date:** [July 22, 2014, 4:00pm UTC](https://community.dreamfactory.com/t/using-inner-join-and-group-by-to-help-filter-a-rest-get-request/190/5 "2014-07-22T16:00:50Z")
It’s a timestamp field according to the database. I’ve got that filter working. Stored procedures sounds interesting, but I would also be interested in how I could do it with the script. ---
**Author:** ![Polgarmx](https://yyz1.discourse-cdn.com/flex035/user_avatar/community.dreamfactory.com/polgarmx/32/64_2.png) [@Polgarmx](https://community.dreamfactory.com/u/Polgarmx)\ **Post date:** [July 22, 2014, 7:36pm UTC](https://community.dreamfactory.com/t/using-inner-join-and-group-by-to-help-filter-a-rest-get-request/190/6 "2014-07-22T19:36:30Z")
In your MySQL you can create a VIEW (with your Select) as follow: [http://dev.mysql.com/doc/refman/5.0/en/create-view.html](http://dev.mysql.com/doc/refman/5.0/en/create-view.html) Then, your view is access like any simple table with a Where parameter. Regards Polgarmx. ---
**Author:** ![bkromhout](https://yyz1.discourse-cdn.com/flex035/user_avatar/community.dreamfactory.com/bkromhout/32/223_2.png) [@bkromhout](https://community.dreamfactory.com/u/bkromhout)\ **Post date:** [July 22, 2014, 9:04pm UTC](https://community.dreamfactory.com/t/using-inner-join-and-group-by-to-help-filter-a-rest-get-request/190/7 "2014-07-22T21:04:43Z")
That’s actually what I ended up deciding to do today, I’d forgotten about it to be honest. I’d still be curious how it could be done with the script though.