MySql select with sub select needed

Posted on 2009-12-22
Last Modified: 2012-05-08
MySql select wanted. I have a table with 4 columns labeled id (auto increment), date (date), type (int), sub_type (int), amount (int). The desired result are the records for each type and corresponding subtype with the most recent date entry. Here is an example.

Without the extra complexity of the sub_type the select would be:

SELECT * FROM  yourtable s1 WHERE  date=(SELECT MAX( FROM yourtable s2 WHERE s1.type = s2.type)
Question by:phillystyle123
    LVL 4

    Accepted Solution

    Without testing it, this would be my first guess...

    SELECT * FROM  yourtable s1 WHERE  date=(SELECT MAX( FROM yourtable s2 WHERE s1.type = s2.type AND s1.sub_type = s2.sub_type)
    LVL 5

    Expert Comment

    this code will give you wat you want
    set nocount on 
    declare @tempo1 table 
    	vID int,
    	vDate datetime,
    	vType int,
    	vSubType int,
    	vPrice int
    insert into @tempo1
    select distinct '','',a.type, a.sub_type,'' from yourtable1 a
    update p
    	set vDate=(select max( from yourtable1 a where a.type = p.vtype and a.sub_type = p.vsubtype)
    from @tempo1 p
    update p
    	set vID=(select a.ID from yourtable1 a where a.type = p.vtype and a.sub_type = p.vsubtype and = p.vDate)
    from @tempo1 p
    update p
    	set vPrice=(select a.Price from yourtable1 a where a.type = p.vtype and a.sub_type = p.vsubtype and = p.vDate)
    from @tempo1 p
    select * from @tempo1

    Open in new window

    LVL 107

    Expert Comment

    by:Ray Paseur
    Sorry I do not have time to test it right now, but I think you want to use GROUP BY.

    Interesting that you would get a different output from that data if you used GROUP BY and ORDERed the rows differently!

    Best, ~Ray

    Author Closing Comment

    this seemed like the simplest of the 2 so i gave it a shot and it worked. thanks!!!

    Featured Post

    Easy Project Management (No User Manual Required)

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
    Generating table dynamically is the most common issue faced by php developers.... So it seems there is a need of an article that explains the basic concept of generating tables dynamically. It just requires a basic knowledge of html and little maths…
    The viewer will learn how to count occurrences of each item in an array.
    The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

    733 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

    23 Experts available now in Live!

    Get 1:1 Help Now