# Need Help For Query



## Bjorn_Of_Iceland (Mar 31, 2011)

MS Sql Query that is ^^..

I cant seem to describe the problem in google..

basically it looks something like this:






Then I just want my result to be like this:






Basically, I want to get only the Suppl With the latest Create date (the maximum date for each suppl.. In this case, suppl 13 has 11:28, suppl 20 has 11:27 etc.. it doesnt include the other Suppl.

Basicall some kind of a select distinct on the suppl, but at the same time, takes the Create in matter as well..

Ive been thinking for hours on this lol. Just want to ask if its possible in sql, otherwise, I'll do in through the app.. which is kind of expensive since instantiating connection is not quite friendly.. there can be a few hundreds of Suppl numbers that might show..


----------



## W1zzard (Mar 31, 2011)

Limit 4 ?


----------



## Bjorn_Of_Iceland (Mar 31, 2011)

oh no hehe.. I wont be limiting it to 4, since it can be any amount..


----------



## W1zzard (Mar 31, 2011)

ORDER BY Create GROUP BY Suppl


----------



## Bjorn_Of_Iceland (Mar 31, 2011)

yep. that would certainly organize it out.. but I just want to remove the extras and leave the top most ones..


----------



## caleb (Apr 1, 2011)

What about HAVING MAX() ?
I mean first selecting MAX and then joining to that. I think its possible somehow with HAVING or maybe the ranking functions but I always go the lame way :

SELECT * FROM A JOIN (SELECT ID,MAX(Create) FROM A GROUP BY ID) B ON A.ID=B.ID


----------



## Thrackan (Apr 1, 2011)

SELECT * FROM  bla
WHERE NOT EXISTS (SELECT * from bla bla2 where bla2.suppl = bla.suppl and bla2.date > bla.date)

EDIT: I know it can be done more efficiently, but this was a quickie.


----------



## smartali89 (Apr 1, 2011)

select *top 4* * from table* order by creation desc*

I suppose you are using ms access, this query will sort the result by (latest at the top) and then give you the four results from the top.


----------



## Thrackan (Apr 1, 2011)

smartali89 said:


> select *top 4* * from table* order by creation desc*
> 
> I suppose you are using ms access, this query will sort the result by (latest at the top) and then give you the four results from the top.



yeah, but if the last 4 results are type '13' then you will not have the top result for each type


----------



## Bjorn_Of_Iceland (Apr 1, 2011)

caleb said:


> What about HAVING MAX() ?
> I mean first selecting MAX and then joining to that. I think its possible somehow with HAVING or maybe the ranking functions but I always go the lame way :
> 
> SELECT * FROM A JOIN (SELECT ID,MAX(Create) FROM A GROUP BY ID) B ON A.ID=B.ID


Holy crap that worked! haha. Thanks for the pro tip 

went something like this:

SELECT * FROM A JOIN (SELECT ID,MAX(Create) _AS Create_ FROM A GROUP BY ID _my where clauses_) B ON A.ID=B.ID _my where clauses_)


----------

