Hello everyone, I've got a bit of an SQL mystery that I'm not sure how to solve. For some reason I just cant get my head around it. Here's the scenario:
Table A:
_____________
BidID - Int identity
AuctionID - int
BiderName - varchar(50)
bidAmount - money
______________________
Now obviously each Bid will have a Unique ID using BidID but the other rows will contain multiple bids per user, on many different items possibly.
BidID AuctionID BiderName BidAmount
1 4005 joeblow 100.00
2 4005 janedoe 101.00
3 4005 joeblow 107.00
4 4006 joeblow 100.00
5 4006 janedoe 105.00
6 4006 joeblow 106.00
I need to find out which Auctions JoeBlow is bidding on, but I dont need a table with Rows for every single one of his bids, just a distinct auctionID for his top bid so in this case the only thing returned would be
3 4005 joeblow 107.00
6 4006 joeblow 106.00
Any clues? I've been through sub querys, and stored procedures, and I cant get anything to work quite right.
SELECT * on a 4000 row table is taking more than 12 seconds. Other larger tables are not nearly as slow. I've DBCC dbreindex'd, and dbcc showcontig shows density at 100%.
How can I figure out why this is happening? What are some remedies?
i just can't find a way to perform this Select Query in my ASP.Net page. I just want to find out the sales for a certain period[startDate - endDate] for each Region that will be selected in the checkbox. Table Sales Fields: SalesID | RegionID | Date | Amount This is how the interface looks like.Thank You.
I have created a trigger that is set off every time a new item has been added to TableA.The trigger then inserts 4 rows into TableB that contains two columns (item, task type).
Each row will have the same item, but with a different task type.ie.
I want to recursively select all records within a hierarchy, using the main parentid and a textvalue on level 1 OR level 2 of the subcategories.
My data:
CREATE TABLE [dbo].[articlegroups]( [id] [int] NOT NULL, [parentid] [int] NOT NULL, [catlevel] [tinyint] NOT NULL, [slug_en] [nvarchar](50) NOT NULL, CONSTRAINT [PK_globos_articlegroups] PRIMARY KEY CLUSTERED
[Code] ...
When selecting rows I always have the main parentId (so catlevel 0) and the slug_en value.
In my example case I have id 129 and slug_en='cradles'.
I want my query to then return:
idparentidcatlevel 12900 1301291 1361302If I have id 129 and slug_en='pillows'.
I want my query to then return:
idparentidcatlevel 12900 1391291
How can I do this? I'm new to SQL Server. I was reading here [URL] .... on recursive SQL, but how to implement this as I just have one table and I also have 2 selection criteria (main category id and a text value on either level 1 or 2).
select CurrencyCode,TransactionCode,TransactionAmount,COUNT(TransactionCode) as [No. Of Trans] from TransactionDetails where CAST(CurrentTime as date)=CAST(GETDATE()as date) group by TransactionCode, CurrencyCode,TransactionAmount order by CurrencyCode
select CurrencyCode,TransactionCode,TransactionAmount,COUNT(TransactionCode) as [No. Of Trans] from TransactionDetails where CAST(CurrentTime as date)=CAST(GETDATE()as date) group by TransactionCode order by CurrencyCode
But of course this codes gives an error, but how can I get my desired result??
SELECT Node_ID,Day,Operation, AA,BB FROM (SELECT CASE WHEN Operation LIKE 'NOTIFY' THEN SUM(Total_request) ELSE 0 END AS AA, CASE WHEN OPERATION LIKE 'SEARCH' THEN SUM(Total_requests) ELSE 0 END AS BB,Node_ID,DAY,Operation
[code]....
So i want to make two columns by the name of operation. in the real code AA and BB are calculates with many counters. My code doesn't work, I have an error: "not a single-group group function" .....
partition with single file group or multiple file group which one best.
we have some report running from partition table, few reports don't have any partition Key and after creating 400 partition with 400 file group it is slow.what is best practices to crate 400 file group or single file group.
Say the following are the columns of a tableA B C D E FCan a aggregate function like sum be applied to A like sum(a) and thenorder by b and c similarly aggregate function on d and group by e andf using a single query...Regards,Jai
a record value instead of aggregated value with GROUP BY?
Assume that I have a PRODUCT_COMMENT table defined as below. It logs the multiple comments for products. A product may have multiple comments logged at different time.
Code Block
CREATE TABLE [dbo].[PRODUCT_COMMENT]( [COMMENT_ID] [int] IDENTITY(1,1) NOT NULL, [PRODUCT_ID] [int] NOT NULL, [COMMENT] [nvarchar](50) NULL, [UPDATED_ON] [datetime] NOT NULL, CONSTRAINT [PK_PRODUCT_COMMENT] PRIMARY KEY CLUSTERED ( [COMMENT_ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
GO ALTER TABLE [dbo].[PRODUCT_COMMENT] WITH CHECK ADD CONSTRAINT [FK_PRODUCT_COMMENT_PRODUCT] FOREIGN KEY([PRODUCT_ID]) REFERENCES [dbo].[PRODUCT] ([PRODUCT_ID]) GO ALTER TABLE [dbo].[PRODUCT_COMMENT] CHECK CONSTRAINT [FK_PRODUCT_COMMENT_PRODUCT]
I would like to use the following SQL statement to get the latest comments for all products.
Code Block
SELECT PRODUCT_ID, COMMENT, UPDATED_ON FROM PRODUCT_COMMENT GROUP BY PRODUCT_ID HAVING UPDATED_ON = MAX(UPDATED_ON)
But this leads to the following error:
Code Block
Column 'PRODUCT_COMMENT.UPDATED_ON' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.
I have a query that pulls back task and user assigned. Each task can have multiple users assigned. I want to pull back the single task and all the users assigned in one row.
Current Query:
select t.Name 'Task', d.FirstName + d.LastName 'User' from [dbo].[Tasks_TemplateAssignTo] a join Task_Template t on a.template_id = t.ID join Doctor d on d.id = a.provider_id
Results from query above:
TaskUser Call CustomerJohn Smith Call CustomerBetty White Call CustomerTammy Johnson Order suppliesGreg Bullard Order suppliesJosephine Gonzalez
Expected Results:
TaskUser Call CustomerJohn Smith, Betty White, Tammy Johnson Order SuppliesGreg Bullard, Jospehine Gonzalez
Hey guys i have a stock table and a stock type table and what i would like to do is say for every different piece of stock find out how many are available The two tables are like thisstockIDconsumableIDstockAvailableconsumableIDconsumableName So i want to,Select every consumableName in my table and then group all the stock by the consumable ID with some form of total where stockavailable = 1I should then end up with a table like thisEpson T001 - Available 6Epson T002 - Available 0Epson T003 - Available 4If anyone can help me i would be very appreciative. If you want excact table names etc then i can put that here but for now i thought i would ask how you would do it and then give it a go myself.ThanksMatt
I am trying to count a column field in a single table and return two count values as one record set using group by.
field1 = group by (department) nvarachar field2 = count (closed) datetime
I have tried using derived tables with no luck getting the desired result.
field2 is a datetime field as indicated I want a count for two conditions
1. WHERE field2 is null 2. WHERE field2 is not null
End Results would like this ====== Department | OpenItems | ClosedItems Department1 | 32 | 24 Departmnet2 | 87 | 46 Department3 | 42 | 76
=======
I got it *almost* working with derived tables, but the group by function was not putting the department as one single row. I was getting multiple rows for departments.
I realize this is probably a simple answer and I am making this a lot harder than it actually is....
I have a need to show a row inside a table group to simulate a header row for the data rows inside the group. The table will not have a real header or footer. Thanks for the help.
I've 2 tables QuestionAnswers and ConditionalQuestions and fetching data from them using CTE join and I'm seeing repetitive rows (not duplicate) like, If you have multiple answers for 1 question, the output is like
where london where paris where toronto
why us why japan why indonesia
I want to eliminate the repetitive question and group them as parent child items.
with cte as ( select cq.ConditionalQuestionID from ConditionalQuestions cq inner join QuestionAnswers qa on cq.QuestionID=qa.QuestionID where cq.QuestionID=5 and qa.IsConditional='Y') select distinct q.Question, a.Answer from QuestionAnswers qa inner join Answers a on a.AnswerID = qa.AnswerID inner join Questions q on q.QuestionID = qa.QuestionID inner join cte c on c.ConditionalQuestionID = qa.QuestionID;
I just need the single select to result the docnbr which is repeating the values. In the above case I want my result like below where the DocNbr 00002 and 00004 repeated their values.
I have a question which was given to me in a test. Question is :: print value from 1-20 in a single column using single select query without using any table. If any one have solution, then please help me.
Just wondering, in DTS, can I run a SELECT script that selects from 2 different databases (both on the same server)?
I can run this in Sql Query Analyzer, but in a DTS, it doesnt accept my database name prefixes:
SELECT a.something FROM DB_ONE.dbo.product a, DB_ONE.dbo.mp_brand b, DB_TWO.dbo.lk_pcat_cutover c WHERE a.PCat <> c.Pcat
(where DB_ONE and DB_TWO are the 2 different db names)
I have one connection to DB_ONE... does this mean I cant access DB_TWO when using this connection? I want to try and avoid using a temporay table for storing DB_TWO's data in DB_ONE... Is this possible?
I hope someone can answer this, I'm not even sure where to start looking for documentation on this. The SQL query I'm referencing is included at the bottom of this post.
I have a query with 3 select statements joined together like tables. It works great, except for the fact that I need to declare a variable and make it a table within two of those 3. The example is below. You'll see that I have three select statements made into tables A, B, and C, and that table A has a variable @years, which is a table.
This works when I just run table A by itself, but when I execute the entire query, I get an error about the "declare" keyword, and then some other errors near the word "as" and the ")" character. These are some of those errors that I find pretty meaningless that just mean I've really thrown something off.
So, am I not allowed to declare a variable within these SELECT tables that I'm creating and joining?
Thanks in advance, Andy
Select * from
(
declare @years table (years int);
insert into @years
select
CASE
WHEN month(getdate()) in (1) THEN year(getdate())-1
WHEN month(getdate()) in (2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12) THEN year(getdate())
END
select
u.fullname
, sum(tx.Dm_Time) LastMonthBillhours
, sum(tx.Dm_Time)/((select dm_billabledays from dm_billabledays where Dm_Month = Month(GetDate()))*8) lasmosbillingpercentage
from
Dm_TimeEntry tx
join
systemuserbase u
on
(tx.owninguser = u.systemuserid)
where
Month(tx.Dm_Date) = Month(getdate())-1
and
year(dm_date) = (select years from @years)
and tx.dm_billable = 1
group by u.fullname
) as A
left outer join
(select
u.FullName
, sum(tx.Dm_Time) Billhours
, ((sum(tx.Dm_Time))
/
((day(getdate()) * ((5.0)/(7.0))) * 8)) perc
from
Dm_TimeEntry tx
join
systemuserbase u
on
(tx.owninguser = u.systemuserid)
where
tx.Dm_Billable = '1'
and
month(tx.Dm_Date) = month(GetDate())
and
year(tx.Dm_Date) = year(GetDate())
group by u.fullname) as B
on
A.Fullname = B.Fullname
Left Outer Join
(
select
u.fullname
, sum(tx.Dm_Time) TwomosagoBillhours
, sum(tx.Dm_Time)/((select dm_billabledays from dm_billabledays where Dm_Month = Month(GetDate()))*8) twomosagobillingpercentage
How should I write the query which can return records with unique email address with any name attached?
The expected record set will be
name email --------- --------- <any> abc@abc.com <any> 123@123.com
I was thinking to select the distinct of email and just stuck at here. I encountered this problem few times and still cannot solve it. Any help will great appreciated. Thanks in advance.
Can somebody please tell me whether the following syntax is supportedor whether it's a "feature" that will someday stop working. It works inboth SQL Server 2000 and 2005 at the moment.declare @var varchar(1000)set @var = ''select @var = @var + colx from some_table where col1 = some_valuecolx is a varchar or at least is cast to one as part of the selectstatement. If the where clause would normally return more than one row,all returned values for colx are concatenated into @var.I've not seen this syntax before but that doesn't make it wrong ;-)Malc.
how to convert float to timestamp in single select query..for exp. i have float as 1.251152515236 ,i want to convert this to datetime and from datetime to timestamp... i.e. 26:11:00
hello, i have two tables: Pictures and UserComments, both have PictureID column in second table i store each comment made by users at a specific picture like so: CommentID, UserName, PictureID, Comment, Date i am trying to make a stored procedure with @UserName input parameter witch returns Distinct Pictures whereon that user has commented (sorry for that whereon expression, is from dictionary and i don't know if it express what i want to mean) SELECT Pictures.OwnerName AS 'Owner', Pictures.Name AS 'Name of picture', COUNT(UserComments.PictureID) AS 'Comments made', Pictures.Image1 FROM Pictures INNER JOIN UserComments ON Pictures.PictureID = UserComments.PictureID WHERE (UserComments.UserName = @UserName) GROUP BY UserComments.UserName, Pictures.Name, Pictures.OwnerName, Pictures.Image1 if i use this statement it shows me the picture details whereon that user has commented and if he commented more than once on the same picture the result isn't duplicated but with that statement i can't order by UserComments.Date because i have to use GROUP BY UserComments.Date and the results will not be shown in pairs How can i order the results by Date Desc? sorry for my bad english, if you don't understand please tell me and i'll try to explain by examples please help me, thanks
