How to get correct data from a query?

Posted on 2013-11-26
Last Modified: 2013-12-26

I have a table like this named Profiles:

!dMain, IdProfile, Qty
1                17        20
2                17        33
3                18        25
4                18        30
5                17        10
6                17         5

I just want to have the last added record per each IdProfile using SQL, something like this:

!dMain, IdProfile, Qty
4                18        30
6                17          5

Thanks in advance.
Question by:dimensionav

Expert Comment

ID: 39677977
do you have a timestamp column, if not add one and query against max(timestamp) for each IdProfile

Expert Comment

ID: 39678024
SELECT Last(Profiles.IDMain) AS LastOfIDMain, Profiles.IDProfile, Last(Profiles.Qty) AS LastOfQty
FROM Profiles
GROUP BY Profiles.IDProfile

LastOfIDMain      IDProfile      LastOfQty
     6                          17             5
     4                          18           30

I don't use MySQL, so I am not sure if this syntax would work in MySQL.
LVL 11

Accepted Solution

Amar Bardoliwala earned 500 total points
ID: 39678071
Hello dimensionav,

if idMain is your primary key you can do something like following

SELECT * FROM profiles p1
p1.idMain IN(SELECT MAX(p2.idMain) FROM profiles p2 group by p2.idProfile);

Hope this will help you.

Thank you.

Amar Bardoliwala

Author Comment

ID: 39740273
Guys, this is a new situation based on this question, maybe you could be interested:

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
In a recent question ( here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question