March 9, 2010 at 11:29 pm
Folks,
Here I struck in one situation where i want to retrieve columns based on one condition, which is numeric
Ok
In simple terms
I have one order table, there is firstname and orders and date columns
so i want to retrieve firstname based on same no of orders placed on a particular date
here is my query
select firstname , orders where orders=(?)
I am trying to write sub query but it is throwing an error
I appreciate ,if any one suggest me what to do
Thanks
Bob
March 10, 2010 at 2:33 am
Hi Bob,
It's not clear what you want. If you want no of orders placed on a particular date, you would need to either add the date in the select clause (if you want to look at several dates) or to the where clause (if you want to see rows from one date only).
To get a good answer you would need to post some sample data and give example of the expected result.
Have a look at this article to get help in creating sample data:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
/Markus
March 10, 2010 at 9:29 pm
Bob,
I am going to contribute the following code, not as a solution, but as a way to help you describe what you want.
How does the following not meet your need?
DECLARE @T
TABLE (
row_id INTEGER PRIMARY KEY,
first_name NVARCHAR(30) NOT NULL,
orders INTEGER NOT NULL,
date_time DATETIME NOT NULL
);
INSERT @T
(row_id, first_name, orders, date_time)
SELECT 1, 'Adam', 5, '20010203' UNION ALL SELECT
2, 'Bob', 5, '20010203' UNION ALL SELECT
3, 'Chris', 7, '20010203' UNION ALL SELECT
4, 'Darren', 7, '20010203' UNION ALL SELECT
5, 'Eve', 5, '20010203' UNION ALL SELECT
6, 'Frank', 5, '20010203';
SELECT first_name, orders
FROM @T
WHERE orders = 5
AND date_time = '20010203';
Paul
March 12, 2010 at 7:53 am
Variation on the above.
DECLARE @T
TABLE (
row_id INTEGER PRIMARY KEY,
first_nameNVARCHAR(30) NOT NULL,
order_id INTEGER NOT NULL,
date_time DATETIME NOT NULL
);
INSERT @T
(row_id, first_name, order_id, date_time)
SELECT 1, 'Adam', 1, '20010203' UNION ALL SELECT
2, 'Adam', 2, '20010203' UNION ALL SELECT
3, 'Chris', 3, '20010203' UNION ALL SELECT
4, 'Eve', 4, '20010203' UNION ALL SELECT
5, 'Eve', 5, '20010203' UNION ALL SELECT
6, 'Frank', 6, '20010203';
DECLARE @order_date DATETIME
DECLARE @order_count INT
SET@order_date = '20010203'
SET@order_count= 2
SELECTa.*
FROM@T a
INNERJOIN (
SELECT first_name,
date_time
FROM@T
WHEREdate_time = @order_date
GROUPBY first_name,
date_time
HAVING COUNT(*) = @order_count
) AS b ON a.first_name = b.first_name AND a.date_time = b.date_time
March 12, 2010 at 9:12 am
Where's Bob?! :w00t:
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply