SQL QUERY to find maximum and minimum column value of a primay key column in a year

Question

Post reply

SQL QUERY to find maximum and minimum column value of a primay key column in a year

SQListic

SSCertifiable

Points: 6904
More actions
April 29, 2013 at 4:19 pm

#394692

Hi Experts,
C_KEY ENTER_DATE
1 2008-12-31 00:00:00.000
2 2008-12-31 00:00:00.000
3 2008-12-31 00:00:00.000
4 2009-12-31 00:00:00.000
5 2009-12-31 00:00:00.000
6 2009-12-31 00:00:00.000
7 2010-12-31 00:00:00.000
8 2010-12-31 00:00:00.000
9 2010-12-31 00:00:00.000
10 2010-12-31 00:00:00.000
can someone help me in finding min and max c_key for all years.
for EG:
YEAR Min C KEY MAX CKEY
2008 1 3
2009 4 6
2010 7 10

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 1

Slightly confused, does this not work?

SELECT

YEAR( Enter_Date) AS yr,

MIN( C_Key) AS MinKey,

MAX( C_Key) AS MaxKey

FROM

Sometable

GROUP BY

Year( Enter_Date)

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Calibear Ten Centuries Points: 1377 More actions · Answer 2

Evil, for us old timies that still work on sql 2005 most of the time, the Year() might not have been the first thing that came to mind. None-the-less, great solution

--------
For SQL Tips, check out my blog: http://lantztechknowledge.blogspot.com/[/url]
You can also follow my twitter account to get daily updates: @BLantz2455

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 3

Calibear (4/29/2013)
Evil, for us old timies that still work on sql 2005 most of the time, the Year() might not have been the first thing that came to mind. None-the-less, great solution

Year is available since 2k5. It wasn't available in 2k, where you had to use datepart.

http://msdn.microsoft.com/en-us/library/ms186313(v=sql.90).aspx

So, if you're still in 2k5, it's available! 🙂 I've only switched up to 2k8 in the last 6 months or so professionally.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Calibear Ten Centuries Points: 1377 More actions · Answer 4

Well, I'll be, I thought it was 2008. Thanks

--------
For SQL Tips, check out my blog: http://lantztechknowledge.blogspot.com/[/url]
You can also follow my twitter account to get daily updates: @BLantz2455