MySql select with sub select needed

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(s2.date) FROM yourtable s2 WHERE s1.type = s2.type)
--
table.jpg
phillystyle123Asked:
Who is Participating?
 
me655321Commented:
Without testing it, this would be my first guess...

SELECT * FROM  yourtable s1 WHERE  date=(SELECT MAX(s2.date) FROM yourtable s2 WHERE s1.type = s2.type AND s1.sub_type = s2.sub_type)
0
 
fhillyer1Commented:
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(a.date) 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 a.date = 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 a.date = p.vDate)
from @tempo1 p

select * from @tempo1

Open in new window

0
 
Ray PaseurCommented:
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
0
 
phillystyle123Author Commented:
this seemed like the simplest of the 2 so i gave it a shot and it worked. thanks!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.