to find the latest value - 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)

to find the latest value

Author

Topic

mangaug
Starting Member

2 Posts

Posted - 2009-11-19 : 02:26:35

There are two tables: Vol and Vol_type

The Vol look like:
sd q ts tp
10/16/2009 600 10/17/2009 B
10/16/2009 500 10/18/2009 B
10/16/2009 500 10/18/2009 A
10/17/2009 600 10/18/2009 B
10/17/2009 500 10/19/2009 B
10/17/2009 500 10/19/2009 A

The Vol_type look like:
tp rank
A 1
B 2

for each distinct sd available in Vol table, first need to see the max rank, if there are two values then I need to take the latest ts.

The output should be:

sd q ts tp
10/16/2009 500 10/18/2009 B
10/17/2009 500 10/19/2009 B

Please help me how to write sql query to I get this.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-11-19 : 04:09:46

[code]
SELECT s.sd,s.q,s.ts,vt.tp FROM (
SELECT v.sd,v.q,v.ts,MAX(vt.rank ) AS rank
FROM (SELECT sd,Max(ts)as ts FROM @vol GROUP BY sd )s
INNER JOIN @vol v on v.sd= s.sd and v.ts = s.ts
LEFT JOIN @vol_type vt ON vt.tp = v.tp
GROUP BY v.sd,v.q,v.ts)S
INNER JOIN @vol_type vt ON vt.rank = s.rank
[/code]

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-11-19 : 04:15:49

Hi

Try this..


CREATE TABLE #VOL(SD DATETIME ,Q INT, TS DATETIME ,TP CHAR(1))


INSERT INTO #VOL
SELECT '10/16/2009', 600 ,'10/17/2009', 'B' UNION ALL
SELECT '10/16/2009', 500 ,'10/18/2009', 'B' UNION ALL
SELECT '10/16/2009', 500 ,'10/18/2009', 'A' UNION ALL
SELECT '10/17/2009', 600 ,'10/18/2009', 'B' UNION ALL
SELECT '10/17/2009', 500 ,'10/19/2009', 'B' UNION ALL
SELECT '10/17/2009', 500 ,'10/19/2009', 'A'

CREATE TABLE #VOL_TYPE(TP CHAR(1), RANK_ID INT)

INSERT INTO #VOL_TYPE
SELECT 'A', 1 UNION ALL
SELECT 'B', 2

SELECT * FROM #VOL
SELECT * FROM #VOL_TYPE


SELECT TP,SD, Q, TS
FROM (
SELECT 
	B.TP,
	A.SD,
	A.Q, 
	A.TS, 
	ROW_NUMBER() OVER (PARTITION BY SD ORDER BY Q) AS RECID
FROM #VOL		A,
	#VOL_TYPE	B	
WHERE A.TP	=	B.TP
) AS D
WHERE RECID = 1

-------------------------
R...

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-11-19 : 04:21:08

Hi, Try this

DECLARE @max_rank INT, @Max_vol VARCHAR(32)
SELECT @max_rank = ( SELECT MAX(ranks) FROM @vol )
SELECT @Max_vol = ( SELECT tp FROM @vol WHERE ranks = @max_rank)

SELECT sd , q , ts , tp FROM
(
SELECT *, ROW_NUMBER() OVER ( PARTITION BY sd ORDER BY q,ts,tp DESC ) AS rid FROM @temp WHERE tp = @Max_vol) x
WHERE x.rid = 1

sql-programmers
Posting Yak Master

190 Posts

Posted - 2009-11-23 : 10:30:20

Try to use this query

create table #temp(sd varchar(100),
Rank int,TS varchar(100))

insert into #temp select sd,max(rank) as Rank,max(ts)
from dbo.Vol inner join Vol_type on Vol_type.tp=Vol.tp
group by sd

select Vol.* from dbo.Vol inner join #temp
on #temp.sd=Vol.sd and #temp.ts=Vol.ts
inner join Vol_type on Vol_type.Rank=#temp.Rank
and Vol_type.tp=Vol.tp

drop table #temp

Or if you are using SQL server 2005 or later, then use this query:

WITH temp AS(
select Vol.sd, Vol.q, Vol.ts, Vol.tp,
ROW_NUMBER() OVER (PARTITION BY SD ORDER BY Q, Vol.ts DESC, Vol_type.[Rank] desc) AS RowID
from Vol
inner join Vol_type
on Vol.tp = Vol_type.tp
)
select sd, q, ts, tp
from temp
where RowID = 1

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources