Need help with Select top 10 & Max(Value)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Need help with Select top 10 & Max(Value)

Author

Topic

beans-zn
Starting Member

3 Posts

Posted - 2008-03-08 : 06:18:49

I have a table called New_Strike_Table
I am executing this select

SELECT TOP 10 DT, Site, DataValue3 AS Expr1
FROM New_Strike_Table
WHERE (Site = 4)
GROUP BY DT, Site, DataValue3

It works however I need the MAX value for the 10 records this statement selected. When I add the

having DataValue3 MAX(DataValue3)

I get an error
Can someone please get me going with this one?

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-08 : 08:07:19

[code]
Select Top 10 a.DT, a.Site, b.MaxDateValue
From New_strike_Table a inner join (Select DT, Site,Max(DateValue3) as MaxDate
FROM new_strike_table
GROUP BY DT, SITE
Order by Max(DateValue) desc) b
on a.DT = b.DT and a.Site = b.Site
[/code]

Presuming you need the highest 10 datevalue's when you are saying Top 10.

Without a qualifier for highest or lowest, it would only return the 1st 10 results, so you have specify the order desc in the subquery so that the join returns the 10 largest values.

Poor planning on your part does not constitute an emergency on my part.

beans-zn
Starting Member

3 Posts

Posted - 2008-03-08 : 08:25:41

Wow this works. This is my first forum quarry and it works. Thanks I appreciate your input.

beans-zn
Starting Member

3 Posts

Posted - 2008-03-08 : 08:38:47

I have a table with a Date (DT), Site number (Site) and Data (Datavalue3).
I need to select the last ten records entered (I use TOP 10 and the DESC for that) and within these ten records I need to select the one with the biggest Datavalue3 in it.
I will try and modify your reply to get this done. At least SQL is not giving me an error with your reply.
Thanks again

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-08 : 08:45:26

Your welcome, but it isn't your query yet. it is still my query, when you modify it successfully to do something else, it can be your query ;)

you could do:

Select Top 1 a.DT, a.Site, b.MaxDateValue
From New_strike_Table a inner join (Select DT, Site,Max(DateValue3) as MaxDate
FROM new_strike_table
GROUP BY DT, SITE
Order by Max(DateValue) desc) b
on a.DT = b.DT and a.Site = b.Site
order by b.MaxDateValue desc

But there is even an easier way...

If you just want the Top 1 record with the highest datevalue3

Select Top 1 DT, SiteNumber,DateValue3
FROM new_strike_table
Order by DateValue3 desc

Poor planning on your part does not constitute an emergency on my part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources