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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Result Set to XML 4 36
Deleting Rows from an Oracle Database - Performance 19 46
SQL Help 27 45
Upgrade MySQL 5.5 to MySQL 5.6 on Windows 13 25
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL ( several years ago, it seemed like now was a good time to updat…
Introduction This article is intended for those who are new to PHP error handling (  It addresses one of the most common problems that plague beginning PHP develop…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …

776 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