SQL MAX query - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

SQL MAX query

Author

Topic

raxbat
Yak Posting Veteran

52 Posts

Posted - 2007-09-27 : 06:21:58

Hello expert! I need some assistance from you.
So, I have a table:
Broker Currency Ask
1 EURUSD 1.3000
2 USDCAD 222
2 EURUSD 1.3022
........
...

I need a query, which returns the MAX ASK values for every broker for every Currency.
Help me pleas do it whith one query!

Thanks a lot!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-09-27 : 06:24:27

[code]select Broker, Currency, max(Ask)
from atable
group by Broker, Currency[/code]

KH
[spoiler]Time is always against us[/spoiler]

raxbat
Yak Posting Veteran

52 Posts

Posted - 2007-09-27 : 07:12:09

Sorry, I formulated my question incorrectly.
The Idea is to get Max ASK value, currency and broker who has this value
so we need to get somth like that:

2 EURUSD 1.234
1 GBPUSD 2.234

ok?

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-09-27 : 07:27:16



-- create our table
declare @foo table (Broker int, Currency varchar(10), Ask decimal(15,4))

-- insert test data
insert into @foo 

select 1,'EURUSD',1.3 union all select
2 ,'USDCAD',222 union all select
2,'EURUSD',1.3022 union all select
3, 'USDCAD',23.23 union all select
3,'EURUSD',1.4

-- do the work
select 
	f.broker,f.currency, f.ask
from 
	@foo f
inner join
(
select  Currency, max(Ask) as Ask
from   @foo
group by  Currency
) a
on 
	f.currency = a.currency 
and 
	f.ask = a.ask

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

raxbat
Yak Posting Veteran

52 Posts

Posted - 2007-09-27 : 07:37:49

rhanks a lot

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-27 : 08:48:41

This is a SQL Server 2005 forum, right?

SELECT	Broker,
	Currency,
	Ask
FROM	(
		SELECT	Broker,
			Currency,
			Ask,
			ROW_NUMBER() OVER (PARTITION BY Currency ORDER BY Ask DESC) AS RecID
		FROM	@Foo
	) AS d
WHERE	RecID = 1

E 12°55'05.25"
N 56°04'39.16"

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-09-27 : 09:03:40

yes. yes it is. I don't know the "new" operators, so I did it the old way.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-27 : 09:07:45

I wasn't putting you down! I questioned OP.

E 12°55'05.25"
N 56°04'39.16"

raxbat
Yak Posting Veteran

52 Posts

Posted - 2007-09-27 : 10:47:06

Another problem! I we have another column Date, I need to select max ASK and MAX date:

SELECT TOP 100 PERCENT dbo.ClearingFarm.broker_id, dbo.ClearingFarm.symbol, dbo.ClearingFarm.ask, dbo.ClearingFarm.lastchange
FROM dbo.ClearingFarm INNER JOIN
(SELECT symbol, MAX(Ask) AS Ask, MAX(lastchange) AS lastchange
FROM clearingfarm
GROUP BY symbol) a ON dbo.ClearingFarm.symbol = a.symbol AND dbo.ClearingFarm.ask = a.Ask
ORDER BY dbo.ClearingFarm.symbol
Results:

broker_id symbol ask lastchange
6 AUDUSD 0.8811 27/09/2007 17:39:07
5 AUDUSD 0.8811 27/09/2007 17:39:08
4 EURGBP 0.7001 27/09/2007 17:39:44
2 EURJPY 163.68 27/09/2007 17:39:48
2 EURUSD 1.4152 27/09/2007 17:39:48
6 EURUSD 1.4152 27/09/2007 17:39:11
5 GBPUSD 2.0227 27/09/2007 17:39:49
2 NZDUSD 0.7509 27/09/2007 17:39:48
6 USDCAD 1.0023 27/09/2007 17:39:11
5 USDCAD 1.0023 27/09/2007 17:39:16
3 USDCAD 1.0023 27/09/2007 17:39:23
2 USDJPY 115.67 27/09/2007 17:39:48

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-27 : 10:50:54

Blimey!
There is absoluteley no guarantee that the two MAXes come from same record!

Do you want the LATEST (max date) and all other relevant information for this record,
or do you want the LARGEST (max ask) and all other relevant information for this record as before?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-27 : 10:53:38

I can prove my point with this simple code


DECLARE	@Sample TABLE (Item INT, Date SMALLDATETIME, Val SMALLMONEY)

INSERT	@Sample
SELECT	1, '20070101', 2000.5 UNION ALL
SELECT	1, '20070925', -110.7 UNION ALL
SELECT	2, '20070923', 2000 UNION ALL
SELECT	2, '20070924', 1000

SELECT		Item,
		MAX(Date) AS mxDate,
		MAX(Val) AS mxVal
FROM		@Sample
GROUP BY	Item
ORDER BY	Item

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-27 : 10:58:28

I posted a perfectly valid SQL Server 2005 query before.
This is how I prove my other point, with same sample data as before.

-- Prepare sample data
DECLARE	@Sample TABLE (Item INT, Date SMALLDATETIME, Val SMALLMONEY)

INSERT	@Sample
SELECT	1, '20070101', 2000.5 UNION ALL
SELECT	1, '20070925', -110.7 UNION ALL
SELECT	2, '20070923', 2000 UNION ALL
SELECT	2, '20070924', 1000

-- Show latest (by date) record per item
SELECT	Item,
	Date,
	Val
FROM	(
		SELECT	Item,
			Date,
			Val,
			ROW_NUMBER() OVER (PARTITION BY Item ORDER BY Date DESC) AS RecID
		FROM	@Sample
	) AS d
WHERE	RecID = 1

-- Show max (by val) record per item
SELECT	Item,
	Date,
	Val
FROM	(
		SELECT	Item,
			Date,
			Val,
			ROW_NUMBER() OVER (PARTITION BY Item ORDER BY Val DESC) AS RecID
		FROM	@Sample
	) AS d
WHERE	RecID = 1