least date value

Question

least date value

Anamika

SSCertifiable

Points: 6202
More actions
June 24, 2010 at 11:35 pm

#220530

Hi,
I'm getting a set of rows after few joins.
from that i want to select only the least date value on a date column.
If i go for MIN function, then I have to group all the other columns. i have huge number of columns.
is this the only method to get the least date on a set of dates or is there any other way we can achieve this?
thanks,
regards,
Ami

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

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

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 1

You can also use the ROW_NUMBER() function, but here again you will have a PARTITION BY clause which is similar to the GROUP BY Clause.

If you need more detailed help, please post the DDL, Insert Scripts and the expected output in proper format as shown in the link in my signature. This will help many people to help you better.

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Anamika SSCertifiable Points: 6202 More actions · Answer 2

thanks for the response. here is the code

CREATE TABLE #TMP (ID INT PRIMARY KEY IDENTITY (1,1), V1 VARCHAR(100))

CREATE TABLE #TMP1 (ID INT , D2 DATETIME)

INSERT INTO #TMP SELECT 'VALUE1'

INSERT INTO #TMP SELECT 'VALUE2'

INSERT INTO #TMP SELECT 'VALUE3'

INSERT INTO #TMP SELECT 'VALUE4'

INSERT INTO #TMP1 SELECT 1, GETDATE()

SELECT T1.ID, T.V1,T1.D2 FROM #TMP T INNER JOIN #TMP1 T1 ON T.ID =T1.ID

DROP TABLE #TMP

DROP TABLE #TMP1

in the above query it gives multiple date columns I want the least date, in this case which is having minimum time i need.

thanks,

Regards

Ami

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 3

This will give you the result you need, but i think the typing effort will be the same if not more because here there is a PARTITION BY clause which is similar to the GROUP BY Clause

SELECTID, V1, D2

FROM(

SELECT ROW_NUMBER() OVER( PARTITION BY T1.ID, T.V1 ORDER BY T1.D2 ) RowNumber, T1.ID, T.V1,T1.D2

FROM#TMP T

INNER JOIN #TMP1 T1 ON T.ID =T1.ID

) T

WHERERowNumber = 1

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Anamika SSCertifiable Points: 6202 More actions · Answer 4

Thanks

actually it saves lot of typing effort.

As I said in the first post. I have lot of columns, I gave example of such scenario as I can't give the full structure and data for it.

Many Thanks,

Regards,

Ami