SQL - How to get specific resut in GROUP BY

Anyone? :(

Try adding

ORDER BY new DESC

Wow, i had tried this but didn't work and now it does, thank you so much. And then I can flip the array with php.

edit: just kidding doesn't work

to expand, if i put it after the order by it doesn't change the result, if i put it before it throws an error.

Can you post the code that is running your query?

Im running it in an SQL manager

I'm not certain your question is clear enough to get you the answer you are looking for - you might want to provide more / clearer information.

Yeah, sorry, but english is not my first language. I'll try to be clearer.

In my table i have a set of rows. When I edit a record i want, instead of update, to create another row with the value 'new' equal to 1, and the same uuid.

Then when i retrive everything I want to get all the "new" data and when isn't available the new data, get the original one.

It's like database versioning but I only care about the last and the first versions, so each edited record will have 2 rows.

I figured out i can retrive the new values (when available) and the old ones with a

SELECT * FROM foobars GROUP BY uuid;

But the result I get is the one from the third example table I posted ( http://s18.postimg.org/gyfe1i0jd/Cattweura.png ), the new values get hidden by GROUP BY, instead i want the original data to get hidden when newer is available, like the second table in the example.

I hope i explained what I mean, sorry again.

I can't see how this would be possible with pure SQL and avoiding subqueries. You would need to select those where new = 1, then UNION it with those where new = 0 and subquery where don't have new = 1

I was wondering if was possible to decide the logic which the GROUP BY function uses. It would be pretty easy with 2 queries and some php.

Mmm ... still not 100% sure of the question, but here goes -

When updating a record, you don't want to overwrite the original. Instead, you want to append (or add) a record, flagging the most recent update (or added row) as being the active, valid record.

If I have understood correctly, I don't think you are going about it the right way. Doing it your way, @henrique is right and straight SQL won't get you there.

But I think I have done something like this before. Instead of using a 'new' column use timestamps and use a correlated subquery in your select statement to only select the most recently added (or update ... the vernacular is a little confusing) records ... that is, the related records with the maximum timedate stamp. That way, you can use straight SQL.

Yeah, you got it, but after the first, any further modification will update the one with the new=1, so i don't really care about timestamps since i will always have one original and one edited.

Thank you for all the replies :)

Yes. But you will need to build logic to create the new=1 and then change everything else to new=0. Using timestamps, you don't have to do any of that.

But if you are content with that, all the best.

Something along these lines will work like you described:

select * from (select * from test
order by new desc) as foo
group by uuid
order by id

You can experiment with it at sqlfiddle.com

Yeah @psmail you are probably right! I'll try your way and see which i like the best. I could also split in 2 tables.