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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Very interesting Access query problem. 13 70
CheckListBox usage 3 51
Trigger usage 2 59
How to base a filter depending on fields contents? 15 49
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Creating and Managing Databases with phpMyAdmin in cPanel.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

867 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now