Need Help For Query

[Bjorn_Of_Iceland]

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]

Limit 4 ?

 

[Bjorn_Of_Iceland]

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

 

[W1zzard]

ORDER BY Create GROUP BY Suppl

 

[Bjorn_Of_Iceland]

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

 

[caleb]

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]

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]

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]

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]

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)