TOP 25 OR all of past 30 days query

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 2008 Forums

Transact-SQL (2008)

TOP 25 OR all of past 30 days query

Author

Topic

kpgraci
Yak Posting Veteran

68 Posts

Posted - 2011-03-17 : 10:34:09

Hi all,

I want to display a list of recent activity within the last 30 days. If the recordcount is less than 25 activities I want to show the last 25 regardless of date.

So I want a query that returns all of the last 30 days no matter how many, and if that recordcount is < 25 then also return additional recent items to make the recordcount 25, assuming they exist.

I can do this with 2 queries. First select all within 30 days, if that count is < 25 then select top 25 ordered by date DESC. Is there a way to combine this in a single query?

thx
kpg

kpg

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-17 : 10:38:27

Please supply us with sample data and expected output.

Jim

Everyday I learn something that somebody else already knew

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-03-17 : 10:41:01

here's one way

SELECT * FROM (
	select top 25 * from yourTable order by [date] desc
)x
union
SELECT * from yourTable WHERE DateDiff(day, [date], getdate()) <= 30

kpgraci
Yak Posting Veteran

68 Posts

Posted - 2011-03-17 : 10:47:04

quote:
Originally posted by jimf

Please supply us with sample data and expected output.

Table will have a creation_date and other items which are of no consequence. Output will be in a datagrid, ordered by creation_date descending, so most recent is on top.

kpg

kpgraci
Yak Posting Veteran

68 Posts

Posted - 2011-03-17 : 11:30:14

I'm sure UNION is the way to go but I've tried several versions of this query and always get an error near UNION. I've tried parenthesising each query and putting a SELECT * FROM (...) UNION (...). What am I doing wrong?

Individually each query works, with a union I get a error.

SELECT TOP 25 CreatedOn FROM MyTable
ORDER BY CreatedOn DESC

UNION

SELECT CreatedOn FROM MyTable
WHERE DateDiff(day, CreatedOn, getdate()) <= 30
ORDER BY CreatedOn DESC

kpg

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-17 : 11:37:34

Do the query just like Russell did.

Jim

Everyday I learn something that somebody else already knew

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-17 : 11:37:49

In a UNION query, ORDER BY cannot appear except at the end of the last statement. Use the query russell posted.

kpgraci
Yak Posting Veteran

68 Posts

Posted - 2011-03-17 : 11:48:08

OK. Used russell's syntax and it works. :)

I would like the result set ordered by CreatedOn. possible?

What is the purpose of the 'x' before UNION??? (I thought it was a typo)

...and... thanks for all your help, this forum is fantastic.

kpg

EDIT: ah, the x is the name of the first result set? yes?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-17 : 11:55:52

Yes, X is an alias for that subquery. You can add "ORDER BY CreatedOn" at the very end.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-17 : 11:56:43

This part here
select top 25 * from yourTable order by [date] desc
is a derived table, and tables must have names. Russell just called it "x"

SELECT * FROM (
select top 25 * from yourTable order by [date] desc
)x
union
SELECT * from yourTable WHERE DateDiff(day, [date], getdate()) <= 30
ORDER BY CreatedOn

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources