We help IT Professionals succeed at work.

Query TOP (N), Declare the most prevalent date as the filter (variable)

Phosphor
Phosphor asked
on
248 Views
Last Modified: 2012-05-08
Hello experts,

Hopefully you can understand what I am looking for. How can I write a query to look at the TOP whatever and declare the most prominent (prevalent) date from the return results as the working filter.
So in my raw data example below, the query would return 09/15/09 as the variable so I could use that as to set the filter.

Thanks for any replies...


Date	|	item
-------------------------------------
10/22/09	|	8000
10/15/09	|	7999
10/01/09	|	7998
09/25/09	|	7997
09/25/09	|	7996
09/25/09	|	7995
09/15/09	|	7994
09/15/09	|	7993
09/15/09	|	7992
09/15/09	|	7991
09/15/09	|	7990
09/15/09	|	7989
09/15/09	|	7988
09/15/09	|	7987
09/15/09	|	7986
09/15/09	|	7985

Open in new window

Comment
Watch Question

Try this.

WHERE DateCol IN(SELECT TOP 1 DateCol FROM YourTable GROUP BY DateCol ORDER BY COUNT(DateCol) DESC)

Greg


Author

Commented:
Hello,
I'm not getting back what I actually need. I think it may be looking at all records where method =5 and the 04/09/07 has the greatest amount of records...

Thanks so far...
My actual query:(Note NEW WHERE clause disabled)
 
SELECT     TOP (200) [Date Printed], [Date Printed] as ADP, Method, Billing, ROW_NUMBER()OVER (ORDER BY [Date Printed]DESC) as rwnum
FROM         dbo.[History Header] 
WHERE     (Method = 5) --AND [Date Printed]IN(Select Top 1 [Date Printed] FROM dbo.[History Header] GROUP BY [Date Printed] ORDER BY Count([Date Printed])DESC)
ORDER BY [Date Printed]DESC
 
RETURNS:(dups removed, see rwnum field as row count for reference)
 
Date Printed            ADP                     Method Billing     rwnum
----------------------- ----------------------- ------ ----------- --------------------
2009-11-05 00:00:00.000 2009-11-05 00:00:00.000 5      17926       1
 
2009-11-03 00:00:00.000 2009-11-03 00:00:00.000 5      1577        2
 
2009-10-26 00:00:00.000 2009-10-26 00:00:00.000 5      15621       9
 
2009-10-12 00:00:00.000 2009-10-12 00:00:00.000 5      16435       14
 
2009-10-07 00:00:00.000 2009-10-07 00:00:00.000 5      20005       20
 
2009-09-29 00:00:00.000 2009-09-29 00:00:00.000 5      18995       49
 
2009-09-24 00:00:00.000 2009-09-24 00:00:00.000 5      17926       52
2009-09-24 00:00:00.000 2009-09-24 00:00:00.000 5      13677       200
 
(200 row(s) affected)
 
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
 
Query using new WHERE clause:
 SELECT     TOP (200) [Date Printed], [Date Printed] as ADP, Method, Billing, ROW_NUMBER()OVER (ORDER BY [Date Printed]DESC) as rwnum
FROM         dbo.[History Header] 
WHERE     (Method = 5) AND [Date Printed]IN(Select Top 1 [Date Printed] FROM dbo.[History Header] GROUP BY [Date Printed] ORDER BY Count([Date Printed])DESC)
ORDER BY [Date Printed]DESC
 
RETURNS: 
 
Date Printed            ADP                     Method Billing     rwnum
----------------------- ----------------------- ------ ----------- --------------------
2007-04-09 00:00:00.000 2007-04-09 00:00:00.000 5      1304        1
2007-04-09 00:00:00.000 2007-04-09 00:00:00.000 5      3387        200
 
(200 row(s) affected)

Open in new window

Do you get the expected date when you run this?

SELECT Top 1 [Date Printed]
FROM dbo.[History Header]
GROUP BY [Date Printed]
ORDER BY Count([Date Printed])DESC

Greg


Author

Commented:
No, it shows 04/09/2007. That particular date returns 1048 rows.

Date Printed
-----------------------
2007-04-09 00:00:00.000

(1 row(s) affected)
I'm confused.  If that date has the most rows, isn't that the one you want?

Greg


Sharath SData Engineer
CERTIFIED EXPERT

Commented:
Among the 200 records returned for "2007-04-09 00:00:00.000", are you looking for only one record? then what is your next criteria to pick that record? (like max(Billing) etc).

If your requirement is to pick only the date field then the post# 25797301 is sufficient. Explain what exactly you are looking for?

Author

Commented:
No, I need it from the largest / most recent date printed. end use is for a report where I don't want to have to set parameters for. Typically we print statements every 2 months and a bunch of one-offs between major printings.

Usually it would be within the top 100-200 in a reverse sorted query.

Thanks for sticking with this.

Author

Commented:
If you refer to the post#  25796744, I show that I want the TOP(200) DESC and then want to return just the greatest count-date from within. There are 148 rows out of 200 with the target date.
Ok, that makes more sense. Try this.



;WITH CTE
AS
(
SELECT     TOP (200) [Date Printed], [Date Printed] AS ADP, Method, Billing, ROW_NUMBER()OVER (ORDER BY [Date Printed]DESC) as rwnum
FROM         dbo.[History Header] 
WHERE     (Method = 5) 
ORDER BY [Date Printed]DESC
)
SELECT * 
FROM CTE
WHERE [Date Printed] = (SELECT TOP 1 [Date Printed] FROM CTE GROUP BY [Date Printed] ORDER BY COUNT([Date Printed]) DESC)

Open in new window

This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Yes, that's a lot closer. I still need the dates greater than the 9/24/09 date.
My idea was to have the query find the date with the greatest count within the top 200, then that sets as a variable to return all records WHERE [date printed] >= variable (In this case 09/24/09)

Author

Commented:
I got it...
changed the line to.
WHERE [Date Printed] >= (SELECT TOP 1 [Date Printed] FROM CTE GROUP BY [Date Printed] ORDER BY COUNT([Date Printed]) DESC)
And that is what I wanted. Thanks a ton.
No problem, change WHERE [Date Printed] = to WHERE [Date Printed] >=.

Greg

 

Author

Commented:
Thanks for helping me out. I can do simple selects, updates and inserts but when it comes to little used functions (for me anyway) I need a little-bit-O-help.

Sincerely...

BTW, I maxxed out the points for your extra effort.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.