Wednesday, September 11, 2013

SQL query to return data corresponding to all values of a column except for the min value of that column

SQL query to return data corresponding to all values of a column except
for the min value of that column

I've a table with the following columns: userid, datetime, type
sample data: userid(1,1,1) datetime(2013-08-01 08:10:00,2013-08-01
08:12:00,2013-08-01 08:12:56) type(I,I,I)
I need to fetch data for only two rows other than the row with min(datetime)
my query to fetch data for min(datetime) is :
SELECT USERID,MIN(CHECKTIME) as ChkTime,CHECKTYPE, COUNT(*) AS CountRows
FROM T1
WHERE MONTH(CONVERT(DATETIME,CHECKTIME)) = MONTH(DATEADD(MONTH, -1,
CONVERT(DATE,GETDATE())))
AND YEAR(CONVERT(DATETIME,CHECKTIME)) = YEAR(GETDATE()) AND USERID=35
AND CHECKTYPE='I'
GROUP BY CONVERT(DATE,CHECKTIME),USERID,CHECKTYPE HAVING COUNT(*)>1
a lil help'll be much appreciated..thnx

No comments:

Post a Comment