Deriving counts and maximum dates

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

Deriving counts and maximum dates

Author

Topic

jj735301
Starting Member

3 Posts

Posted - 2012-03-28 : 16:22:36

Hello,

I appoligize for hijacking a forum topic I thought related to this one.
I appreciate your expertise and time too.
Can you please help me derive results from:

id|entry id
x|12
x|10
x|9
x|8
t|11
t|10
t|9

Query result will produce counts of id entries, id, latest entry, next to latest entry. This would be the actual result from above.
4|x|12|10
3|t|11|10

Hope that makes sense

Thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-28 : 16:29:46

[code]
SELECT COUNT(*) AS cnt,
id,
MAX(CASE WHEN Rn=1 THEN entryid END) AS Latest,
MAX(CASE WHEN Rn=2 THEN entryid END) AS NextLatest
FROM
(SELECT *,ROW_NUMBER() OVER (PARTITION BY id ORDER BY entry_Id DESC) AS Rn
FROM table
)t
GROUP BY id
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jj735301
Starting Member

3 Posts

Posted - 2012-03-29 : 14:49:22

Ha! Awesome work! You guys are the best!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-29 : 14:58:57

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources