Group by to limitation - 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)

Group by to limitation

Author

Topic

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2008-02-01 : 06:02:38

Hello

iam new to this forum. i need a urgent help

i have table which has date column

i used group by to filter how many times date has occured in that table using count(*) and i get result as

2008-02-01 00:00:00.000 4
2008-01-31 00:00:00.000 4
2008-01-10 00:00:00.000 1
2008-01-08 00:00:00.000 1
2007-12-19 00:00:00.000 4
2007-12-18 00:00:00.000 2
2007-12-15 00:00:00.000 2

but what i actually need is that o/p should be displayed for total count < 10
i.e
2008-02-01 00:00:00.000 4
2008-01-31 00:00:00.000 4
2008-01-10 00:00:00.000 1
2008-01-08 00:00:00.000 1
----------------------------
10

i think u can understand my point . u can see the last 3 dates are not getting displayed in this latest o/p since total count exceed 10
how can i achieve it

my query is

SELECT DISTINCT CAST(CONVERT(VARCHAR(15),DCOUPONDATE,101) AS DATETIME),COUNT(*)
FROM COUPONS
GROUP BY CAST(CONVERT(VARCHAR(15),DCOUPONDATE,101) AS DATETIME)
ORDER BY CAST(CONVERT(VARCHAR(15),DCOUPONDATE,101) AS DATETIME) DESC

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-02-01 : 08:13:42

can you post your table DDL with sample data in insert into format ?

KH
[spoiler]Time is always against us[/spoiler]

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2008-02-04 : 03:04:15

CREATE TABLE REQUESTTABLE
(
IDS INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
REQUESTNO VARCHAR(10),
REQUESTMSG VARCHAR(1000)
CREATEDDATE DATETIME
)

THIS IS MY TABLE

AND I INSERT IT AS FOLLOWS

INSERT INTO REQUESTTABLE VALUES('123','REQUEST1',GETDATE())
INSERT INTO REQUESTTABLE VALUES('124','REQUEST2',GETDATE())
INSERT INTO REQUESTTABLE VALUES('125','REQUEST3',GETDATE()-1)
INSERT INTO REQUESTTABLE VALUES('126','REQUEST4',GETDATE()-1)
INSERT INTO REQUESTTABLE VALUES('127','REQUEST5',GETDATE())
INSERT INTO REQUESTTABLE VALUES('128','REQUEST6',GETDATE()-2)
INSERT INTO REQUESTTABLE VALUES('129','REQUEST7',GETDATE())
INSERT INTO REQUESTTABLE VALUES('127','REQUEST5',GETDATE()-1)
INSERT INTO REQUESTTABLE VALUES('128','REQUEST6',GETDATE()-2)
INSERT INTO REQUESTTABLE VALUES('129','REQUEST7',GETDATE())

U CAN SEE THAT TODAY'DATE HAS 5 RECORDS, YESTERDAYS DATE HAS 3 AND DAY BEFORE YESTERDAY HAS 2

U CAN SEE FROM MY STARTER THREAD I HAVE WRITTEN A QUERY
QUERY EXECUTES AS FOLLOWS AS
----------------
4/2/2008 5
3/2/2008 3
2/2/2008 2

BUT I NEED A RESULT AS SOMETHING LIKE THIS WHAT ARE DATES PRESENT WITHIN TOP 6 RECORDS[DISTINCT DATES] AND SO THERE COUNT AS SOEMTHING LIEK THIS

4/4/2007 5
3/2/2008 1 [ NOT AS 3 AS PER ABOVE RESULT SINCE 5+1 EQUATES TO 6]

THANK YOU

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-02-11 : 00:19:11

[code]SELECT DATEADD(DAY, DATEDIFF(DAY, 0, CREATEDDATE), 0), COUNT(*)
FROM
(
SELECT TOP 6 *
FROM @REQUESTTABLE
ORDER BY CREATEDDATE DESC
) a
GROUP BY DATEADD(DAY, DATEDIFF(DAY, 0, CREATEDDATE), 0)[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources