Select with Subquery Logic - 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

General SQL Server Forums

New to SQL Server Programming

Select with Subquery Logic

Author

Topic

workindan
Starting Member

21 Posts

Posted - 2011-03-24 : 12:36:13

Hi, fairly new to SQLsvr 2008. I'm trying to do a query that looks at two things to bring back results.

Data:
[Customer],[Widget],[OrderDate], (bunch of other fields)
Joe,424,3/23/2011
Joe,155,3/23/2011
Mary,155,3/23/2011
Steve,289,3/21/2011
Kevin,133,3/21/2011
Kevin,988,3/21/2011

Basically I'm trying to select the TOP 75 customers based on ORDERDATE, however I'm trying to prioritize those with multiple widget orders before those with single orders.

I've tried:
Select top 75 Customer,Widget,OrderDate
From tblOrders
Where Customer in (select top 1000 Customer, Count(Customer) order by Count(customer) desc)
Order by OrderDate desc

I've also tried creating a view with the Select Customer,Count(Customer) SQL and then doing an inner join:
Select top 75 Customer, Widget, OrderDate
From tblOrders inner join viewMultiWidget on .customer = .customer
(blah blah)

But both of those methods either fail or return only a few results. Even if I increase the subquery to select top (total records in table), the results aren't prioritized by Multiple Widgets.

Basicaly all the fields should populate, but prioritized by:
1) OrderDate then 2) Customers with Multiple Widgets

So....how do I select the most recent orders, but prioritize those with multiple widgets per order?

Any help would be welcome! I'm on Sql Svr 2008

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-03-24 : 13:31:49

It sounds like you want to 'limit' your results by OrderDate, and then Order by widget count...

So maybe something like:

Select top 75
blah,blah
From tblOrders A
Inner join viewMultiWidget B
On A.Customer = B.Customer
Where OrderDate > getdate()-5 --or whatever is recent
Order By widgetCount desc

Corey

snSQL on previous signature "...Oh and by the way Seventhnight, your signature is so wrong! On so many levels, morally, gramatically and there is a typo!"

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-03-24 : 19:35:24

Please post real DDL. Learn to use ISO-11179 rules for the data element names, avoid needless dialect and use ISO-8601 temporal formats, codes and so forth. People cannot read your mind, so post your code and clear specs if you really want help.

In the future, learn why columns not fields and just how silly “tbl-” on a table name looks. And where is the key? I am going to guess that you actually have an order number as the key. And where is the attribute property on “widget”? widget_qty, widget_nbr, widget_size or what? Based on nothing you told us, I will guess it is a part number. See why DDL is basic Netiquette?

CREATE TABLE Orders
(order_nbr INTEGER NOT NULL PRIMARY KEY,
customer_name VARCHAR(25) NOT NULL,
widget_nbr INTEGER NOT NULL,
order_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL);

INSERT INTO Orders
VALUES (1, 'Joe', 424, '2011-03-22'),
(2, 'Joe', 155, '2011-03-22'),
(3, 'Mary', 155, '2011-03-23'),
(4, 'Steve', 289, '2011-03-21'),
(5, 'Kevin', 133, '2011-03-21'),
(6, 'Kevin', 988, '2011-03-21');

>> Basically I'm trying to select the TOP 75 customers based on order_date, however I'm trying to prioritize those with multiple widget orders before those with single orders. <<

WITH Aggregated_Orders (order_date, customer_name, order_cnt)
AS
(SELECT order_date, customer_name, COUNT(order_nbr)
FROM Orders
GROUP BY order_date, customer_name),

Sorted_Orders (order_date, customer_name, order_cnt, order_sort)
AS
(SELECT order_date, customer_name, order_cnt,
DENSE_RANK()
OVER (PARTITION BY order_date
ORDER BY order_cnt DESC, customer_name ASC)
FROM AggregatedOrders)

SELECT order_date, customer_name, order_cnt, order_sort
FROM Sorted_Orders
HAVING order_sort <= 75;

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

workindan
Starting Member

21 Posts

Posted - 2011-03-25 : 17:08:22

Seventhnight your solution worked great! Sorting by the view's widget count was a great idea.

Jcelko, I'm not sure what DDL is, but I posted generic code for confidentiality reasons, and it's more of a logic question, not syntax or anything, so all that was needed was some generalized data. Thank you for replying though!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources