Selecting most recent date - 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

General SQL Server Forums

New to SQL Server Programming

Selecting most recent date

Author

Topic

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2008-01-08 : 09:33:50

I need to select the most recent price for an item from this table where certain criteria are met:

flexing_stock_transactions
--------------------------

item date price status week firm_or_commission
---- -------- ----- ----- ---- ------------------
CH 10/05/07 200 2 35 F
AL 10/05/07 195 1 35 C
CH 10/05/07 209 1 35 F
CH 11/05/07 210 1 35 C

I am currently using:

SELECT
price, date

FROM flexing_stock_transactions

WHERE (
[date] = (SELECT MAX([date]) FROM flexing_stock_transactions)
AND item = 'CH'
AND status = '1'
AND week = '35'
AND firm_or_commission = 'F'
)

zero rows are returned wheras I was expecting:

209, 10/05/07

I'm using MS Server 2005

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-08 : 09:50:08

[code]SELECT
price, date
FROM flexing_stock_transactions f
INNER JOIN (
SELECT MAX([date]) AS 'date'
FROM flexing_stock_transactions
WHERE item = 'CH'
AND status = '1'
AND week = '35'
AND firm_or_commission = 'F'
GROUP BY item
)t
WHERE t.item=f.item
AND t.date=f.date[/code]

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2008-01-08 : 10:07:24

Thanks for that. But it gives me the error:

Incorrect syntax near the keyword 'WHERE' (LINE 14).

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-08 : 10:13:14

SELECT
price, date
FROM flexing_stock_transactions f
INNER JOIN (
SELECT MAX([date]) AS 'date'
FROM flexing_stock_transactions
WHERE item = 'CH'
AND status = '1'
AND week = '35'
AND firm_or_commission = 'F'
GROUP BY item
)t
ON t.item=f.item
AND t.date=f.date

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2008-01-08 : 10:31:08

Thanks for trying, but that gives me:

Msg 207, Level 16, State 1, Line 13
Invalid column name 'item'.
Msg 209, Level 16, State 1, Line 2
Ambiguous column name 'date'.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-01-08 : 10:48:05

modified from visakh16's query

SELECT	f.price, 
	f.date
FROM 	flexing_stock_transactions f
	INNER JOIN 
	(
		SELECT 	item, MAX([date]) AS 'date'
		FROM 	flexing_stock_transactions
		WHERE 	status 			= '1'
		AND 	week 			= '35'
		AND 	firm_or_commission	= 'F'
		GROUP BY item
	)t
	ON 	t.item	= f.item
	AND 	t.date	= f.date
WHERE	f.item	= 'CH'

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

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2008-01-08 : 11:00:27

Thanks visakh16 and khtan, that's nearly got it.

It's running OK, but returns more than 1 row.

To be clear, what I'm looking to get is the last single price we paid for item 'CH' where firm_or_commission = 'F', status = 1 and week = 35

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-08 : 11:05:55

Try this:-
SELECT
f.price, f.date
FROM flexing_stock_transactions f
INNER JOIN (
SELECT MAX([date]) AS 'date'
FROM flexing_stock_transactions
WHERE item = 'CH'
AND status = '1'
AND week = '35'
AND firm_or_commission = 'F'
GROUP BY item
)t
ON t.item=f.item
AND t.date=f.date

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2008-01-08 : 11:22:05

Now I get

Msg 207, Level 16, State 1, Line 13
Invalid column name 'item'.

We're sniffing around the solution. Nearly there, but not quite.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-08 : 11:25:32

Use SQL Server 2005 syntax.

SELECT	d.Item,
	d.Date,
	d.Price,
	d.Status,
	d.Week,
	d.Firm_Or_Commission
FROM	(
		SELECT	Item,
			Date,
			Price,
			Status,
			Week,
			Firm_Or_Commission,
			ROW_NUMBER() OVER (PARTITION BY Item ORDER BY Date DESC) AS RecID
		FROM	Flexing_Stock_Transactions
	) AS d
WHERE	d.RecID = 1

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-01-08 : 11:25:45

Only single record you want ?
a simple TOP 1 will do

SELECT	TOP 1
	f.price, 
	f.date
FROM 	flexing_stock_transactions f
WHERE 	status 			= '1'
AND 	week 			= '35'
AND 	firm_or_commission	= 'F'
AND	f.item			= 'CH'
ORDER BY f.date DESC

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-08 : 11:26:36

Add appropriate WHERE's to the suggestion above.

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-08 : 11:27:33

Ah my bad copy pasting!

SELECT
f.price, f.date
FROM flexing_stock_transactions f
INNER JOIN (
SELECT item,MAX([date]) AS 'date' 
FROM flexing_stock_transactions
WHERE item = 'CH'
AND status = '1'
AND week = '35'
AND firm_or_commission = 'F'
GROUP BY item
)t
ON t.item=f.item
AND t.date=f.date

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2008-01-08 : 11:34:23

Thanks everyone!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources