This is my query:SELECT tblMessages.id, tblMessages.txtIngress, tblMessages.strSubject, tblMessages.txtMessage, tblMessages.postedBy, tblMessages.datePosted,
tblMessages.intMessGroup
FROM tblMessages INNER JOIN
tblUsersToGroups ON tblMessages.intMessGroup = tblUsersToGroups.belongsToGroup INNER JOIN
tblUsers ON tblUsersToGroups.userId = tblUsers.id
WHERE (tblUsers.userName = @strUserName)
GROUP BY tblMessages.intMessGroup, tblMessages.id, tblMessages.txtIngress, tblMessages.strSubject, tblMessages.txtMessage, tblMessages.postedBy,
tblMessages.datePosted
ORDER BY tblMessages.intMessGroupWhat I want to do is select the top 3 from each intMessGroup. In other cases where I'd want to Group By a computed column I could have used Rank, but since the column intMessGroup contains static values (message group id's) there should be some easy peasy way to do this, or?Thanks beforehand for any and all replies!Cheers!/Eskil
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
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
hai guys...am new to sql....plz help me out........ the below is my table structure.....Table Name : #temp pgm_main_category_id pgm_sub_category_id filepath 17 15 photo/Writer/Content/Sports/Basketball/bb1.jpg 17 16 photo/Writer/Content/Sports/Cricket/cricket1.jpg 17 17 photo/Writer/Content/Sports/BaseBall/base1.jpg 18 18 photo/Writer/Content/Nature/Forest/forest1.jpg 18 19 photo/Writer/Content/Nature/Tree/tree1.jpg 18 20 photo/Writer/Content/Nature/Flower/flower1.jpg 19 21 photo/Writer/Content/Gadget/Laptop/laptop1.jpg 19 22 photo/Writer/Content/Gadget/DigitalCamera/camer1.jpg 19 23 photo/Writer/Content/Gadget/Mobile/cybermbl1.jpg 17 24 photo/Writer/Content/Sports/Formula1/F1.jpg from this table i need the query output as below......for example: This is my expected output pgm_main_category_id pgm_sub_category_id filepath
17 15 photo/Writer/Content/Sports/Basketball/bb1.jpg 18 18 photo/Writer/Content/Nature/Forest/forest1.jpg 19 21 photo/Writer/Content/Gadget/Laptop/laptop1.jpg 17 16 photo/Writer/Content/Sports/Cricket/cricket1.jpg 18 19 photo/Writer/Content/Nature/Tree/tree1.jpg 19 22 photo/Writer/Content/Gadget/DigitalCamera/camer1.jpg 17 17 photo/Writer/Content/Sports/BaseBall/base1.jpg 18 20 photo/Writer/Content/Nature/Flower/flower1.jpg 19 23 photo/Writer/Content/Gadget/Mobile/cybermbl1.jpg 17 24 photo/Writer/Content/Sports/Formula1/F1.jpg if anybody have an idea about this query plz send me...... i need this immediately guys.....thanks in advance......regards janu
Hello,I have two tables:Student (containing columns: name, id, status, amount)Uplate (containing columns: id, student_id as foreign key, date, payement)Students make payment from time to time and that payment goes to Uplate.How to list (What is SELECT statement) to getevery studentits idstatusamounttotal payment made till today! (like sum all payement for this student)Thanks in advance.
I need to find the first 3 days that a product is scheduled in one of my tables. It may be scheduled on 12 different days, but I only want the first 3.
So, based on some sample data and this thread:
http://www.dbforums.com/t1115304.html
I was able to come up with a query that does what I need. However, I have no idea how it works. For one, what is Count(*) counting in the query?
insert into #tmpsched select '1/1/2001', 'abc' union all select '1/2/2001','abc' union all select '1/3/2001','abc' union all select '1/1/2001', 'def' union all select '1/2/2001','def' union all select '1/1/2001', 'ghi' union all select '1/1/2001', 'jkl' union all select '1/2/2001','jkl' union all select '1/3/2001','jkl' union all select '1/4/2001','jkl'
SELECT a.SCHED_DATE, a.PRODUCT FROM #tmpSched a INNER JOIN #tmpSched b ON a.product = b.product AND a.sched_date > = b.sched_date GROUP BY a.product, a.sched_date HAVING COUNT(*) <= 3 order by a.product, a.sched_date
drop table #tmpsched
Any explanation would be appreciated.
EDIT: Wanted first 3, not last 3. Changed "a.sched_date < = b.sched_date" to a.sched_date > = b.sched_date
I'm trying to find the most effective way to get the top N of each group in a group byI've already try the ROW_NUMBER() OVER PARTITION Method but it is still slow ...I've also checked the CROSS APPLY Method not really more efficient..Bu t My group is very simple and limited to one column...So Now I'm on the way to use a stored procedure make a loop of TOP n for each distinct element of my column
hi i have a database that has multiple records under the same id (each time a record is updated, it's assigned a date_value), i'm wanting one row to be generated using the data from the most recent date_value field. a sample of the database looks something like this: ID date_value data1 24 march info11 25 march info2 my statement looks like this:select max(date_value), data from table_name where data is not null group by id this seems to bring up the following error for all the fields not in the group by part of the code:Column 'COLUMN_NAME' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. what am i doing wrong? TIA!
I have this query. SELECT ForeignKey, MAX(Version) AS Latest_VersionFROM tblContentWHERE (Published = 'True')GROUP BY ForeignKeyORDER BY ForeignKey It works perfectly just like it is above. It returns 20 records choosing the maximum value in the version column for each value in the ForeignKey column. Here is a sample of the result set. ForeignKey Latest_Version 1 2 2 2 3 1 Then when I try to add other fields to the select and group by clauses I hit a problem. Here is the new query SELECT ForeignKey, MAX(Version) AS Latest_Version, TitleFROM tblContentWHERE (Published = 'True')GROUP BY ForeignKey, TitleORDER BY ForeignKey Now it is returning 22 records in the result set Here are the relevant records. ForeignKey Latest_Version Title 1 1 Title1 1 2 Title2 2 1 Title3 2 2 Title4 3 1 Title5 I know it is necessary to add each column in the select clause to the group by clause but that seems make group by not work at all. Am I going about this the wrong way. Basically I want to return only the ForeignKey records that have the highest version. This table does have a foreign key relationship with another table. Should I be doing this with a join instead somehow? Thanks, Laura
I am trying to return a result set that gives me the top 20 results for EACH group (PERILNAME in this case), not the top 20 results of the whole result set.
I have been able to create the resluts I want using UNION, but this is not practical when there is more than a few groups. Below is the code the returns the results I am after, any ideas how to achieve this another way?
SELECT TOP 20 LookupPeril.PERILNAME, accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, SUM(loccvg.VALUEAMT) AS [Total Value], COUNT(loc.LOCID) AS [Num Locs],loccvg.VALUECUR FROM loc INNER JOIN loccvg ON loc.LOCID = loccvg.LOCID INNER JOIN accgrp ON loc.ACCGRPID = accgrp.ACCGRPID INNER JOIN RMS_Reports.dbo.LookupPeril LookupPeril ON loccvg.PERIL = LookupPeril.PERILCODE WHERE LookupPeril.PERILNAME = 'Earthquake' GROUP BY accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, LookupPeril.PERILNAME, loccvg.VALUECUR UNION SELECT TOP 20 LookupPeril.PERILNAME, accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, SUM(loccvg.VALUEAMT), COUNT(loc.LOCID) ,loccvg.VALUECUR FROM loc INNER JOIN loccvg ON loc.LOCID = loccvg.LOCID INNER JOIN accgrp ON loc.ACCGRPID = accgrp.ACCGRPID INNER JOIN RMS_Reports.dbo.LookupPeril LookupPeril ON loccvg.PERIL = LookupPeril.PERILCODE WHERE LookupPeril.PERILNAME = 'Windstorm' GROUP BY accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, LookupPeril.PERILNAME, loccvg.VALUECUR UNION SELECT TOP 20 LookupPeril.PERILNAME, accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, SUM(loccvg.VALUEAMT), COUNT(loc.LOCID) ,loccvg.VALUECUR FROM loc INNER JOIN loccvg ON loc.LOCID = loccvg.LOCID INNER JOIN accgrp ON loc.ACCGRPID = accgrp.ACCGRPID INNER JOIN RMS_Reports.dbo.LookupPeril LookupPeril ON loccvg.PERIL = LookupPeril.PERILCODE WHERE LookupPeril.PERILNAME = 'Tornado/Hail' GROUP BY accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, LookupPeril.PERILNAME, loccvg.VALUECUR UNION SELECT TOP 20 LookupPeril.PERILNAME, accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, SUM(loccvg.VALUEAMT), COUNT(loc.LOCID) ,loccvg.VALUECUR FROM loc INNER JOIN loccvg ON loc.LOCID = loccvg.LOCID INNER JOIN accgrp ON loc.ACCGRPID = accgrp.ACCGRPID INNER JOIN RMS_Reports.dbo.LookupPeril LookupPeril ON loccvg.PERIL = LookupPeril.PERILCODE WHERE LookupPeril.PERILNAME = 'Flood' GROUP BY accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, LookupPeril.PERILNAME, loccvg.VALUECUR ORDER BY LookupPeril.PERILNAME, [Total Value]
I am trying to return a result set that gives me the top 20 results for EACH group (PERILNAME in this case), not the top 20 results of the whole result set.
I have been able to create the results I want using UNION, but this is not practical when there is more than a few groups. I think it should be possible using a derived table, but I am not sure how to do it! Below is the code the returns the results I am after, any ideas how to achieve this another way?
SELECT TOP 20 LookupPeril.PERILNAME, accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, SUM(loccvg.VALUEAMT) AS [Total Value], COUNT(loc.LOCID) AS [Num Locs],loccvg.VALUECUR FROM loc INNER JOIN loccvg ON loc.LOCID = loccvg.LOCID INNER JOIN accgrp ON loc.ACCGRPID = accgrp.ACCGRPID INNER JOIN RMS_Reports.dbo.LookupPeril LookupPeril ON loccvg.PERIL = LookupPeril.PERILCODE WHERE LookupPeril.PERILNAME = 'Earthquake' GROUP BY accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, LookupPeril.PERILNAME, loccvg.VALUECUR UNION SELECT TOP 20 LookupPeril.PERILNAME, accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, SUM(loccvg.VALUEAMT), COUNT(loc.LOCID) ,loccvg.VALUECUR FROM loc INNER JOIN loccvg ON loc.LOCID = loccvg.LOCID INNER JOIN accgrp ON loc.ACCGRPID = accgrp.ACCGRPID INNER JOIN RMS_Reports.dbo.LookupPeril LookupPeril ON loccvg.PERIL = LookupPeril.PERILCODE WHERE LookupPeril.PERILNAME = 'Windstorm' GROUP BY accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, LookupPeril.PERILNAME, loccvg.VALUECUR UNION SELECT TOP 20 LookupPeril.PERILNAME, accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, SUM(loccvg.VALUEAMT), COUNT(loc.LOCID) ,loccvg.VALUECUR FROM loc INNER JOIN loccvg ON loc.LOCID = loccvg.LOCID INNER JOIN accgrp ON loc.ACCGRPID = accgrp.ACCGRPID INNER JOIN RMS_Reports.dbo.LookupPeril LookupPeril ON loccvg.PERIL = LookupPeril.PERILCODE WHERE LookupPeril.PERILNAME = 'Tornado/Hail' GROUP BY accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, LookupPeril.PERILNAME, loccvg.VALUECUR UNION SELECT TOP 20 LookupPeril.PERILNAME, accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, SUM(loccvg.VALUEAMT), COUNT(loc.LOCID) ,loccvg.VALUECUR FROM loc INNER JOIN loccvg ON loc.LOCID = loccvg.LOCID INNER JOIN accgrp ON loc.ACCGRPID = accgrp.ACCGRPID INNER JOIN RMS_Reports.dbo.LookupPeril LookupPeril ON loccvg.PERIL = LookupPeril.PERILCODE WHERE LookupPeril.PERILNAME = 'Flood' GROUP BY accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, LookupPeril.PERILNAME, loccvg.VALUECUR ORDER BY LookupPeril.PERILNAME, [Total Value]
I need some help in returning the top 3 records in each group ie I have a table containing a list of stores with the address details and I need to return the top 3 records in each postcode area. The Postcode field contains the postcode area ie BN1, BN2 etc. Many thanks.
I have an aggregated table called T1 which looks like this:
Code:
Name Amount a 10 b -4 c 9 d 5 e 0 f 0 g 8 h 3 i 3
I need to write a query which selects the top 3 names by amount which have an amount greater than 0. The remaining names which have amounts greater than 0 should be grouped together as 'others' at the bottom. Finally I need to add a total row. So the final result I am looking for is:
Code: Name Amount a 10 c 9 g 8 others 11 total 38
The part I'm struggling with is how to collapse the remaining names into the 'others' group.
select 'PURCHASE' as EntityName, d.PartnerName, h.* from ( select MAX([TimeStamp]) [Data import], COUNT(1) [Numar de inregistrari], StartDate, EndDate, DistributorId from DataImport.PurchaseHistory group by DistributorId, StartDate, EndDate ) h inner join Partner d on d.PartnerId = h.DistributorId where d.Active = 1 order by DistributorId, StartDate desc, EndDate desc
I'm looking for some sql syntax that will return the last entry per group in a secondary table. MEANING: have 2 tables, one with names and the other with visits. I need to be able to display all the patients with there last visits.
I have for example a table with columns name, surname, id, ..., weight, age. I need to choose from each age group (GROUP BY age) entire record of the person who has the greatest weight. How to construct such a query?
CREATE TABLE #TestTable ( Pk INT, GroupID INT, Enabled BIT
[code]..
I need to write a select query that will retrieve any GroupID in which every record has an Enabled value of 1.In the example I've provided, only GroupID 1 and 3 will be returned since GroupID 2 has a record with an Enabled value of 0.What would be the most efficient way to write such a query?
Hi,I am bloody amateure and I was wondering if someone could help me editthe statement below so it groups the field SCDMASTER.SCTY_CLASS_CODEand creates one field called "total balance" equallingCUSTODY_BALANCE.OPENING_BALANCE + CUSTODY_BALANCE.DEPOSIT_AMOUNT -CUSTODY_BALANCE.WITHDRAWAL_AMOUNTSELECTCUSTODY_BALANCE.APPLICATIONCYCLEDATE,CUSTODY_BALANCE.ASSET_ID,SCDMASTER.SCTY_CLASS_CODE,CUSTODY_BALANCE.OPENING_BALANCE,CUSTODY_BALANCE.DEPOSIT_AMOUNT,CUSTODY_BALANCE.WITHDRAWAL_AMOUNTFROMFPMCAPSHIST.CUSTODY_BALANCE CUSTODY_BALANCE,CAPSREPORT.SCDMASTER SCDMASTERWHERECUSTODY_BALANCE.ASSET_ID = SCDMASTER.CUSIP_ID AND((CUSTODY_BALANCE.APPLICATIONCYCLEDATE={ts '2007-03-21 00:00:00'}) AND(SCDMASTER.SCTY_CLASS_CODE Not In('BILL','NOTE','BOND','CD','BD','CB','TINT','TPRN' ,'CA','YD')))Thanks,Andreas
I have a question about selecting only the first record in a group. Example: I have table A with primary key = 999. Table B has multiple records with primary key = 999. How can I match Table A 999 with the first occurrence of 999 in Table B, and then extract other field data (such as street address) from the Table B record. I have tried using the Count() function, but it seems that I can only do this using cursors.
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. Thanks in advance for your help.
Two tables: CompanyPrices(CompanyID, ProductID, Price), CompanyRegion(CompanyID, Region) ProductID is the primary key.
I want to get 10 smallest prices in each Region. In other words, I am looking for 10 cheapest prices in each region. So, if there are 20 regions, I should get excatly 200 rows having prices for products from 200 companies if there were at least 10 companies in each region.
I tried the follwoing, but get incorrect results.
select S1.Region, S1.Price from (select CompanyPrices.*, Region from CompanyPrices inner join CompanyRegion on CompanyPrices.CompanyID=CompanyRegion.CompanyID) S1 inner join (select CompanyPrices.*, Region from CompanyPrices inner join CompanyRegion on CompanyPrices.CompanyID=CompanyRegion.CompanyID) S2 on S1.Region = S2.Region group by S1.Region, S1.Price having count(*)<=10 order by S1.Region, S1.Price
However, if I want to get 10 cheapest products for each company, the above sql works by modifying the join condition. Instead of S1.Region = S2.Region , I use S1.CompanyID = S2.CompanyID and I get correct results for 10 cheapest products for each company.
select S1.CompanyID, S1.Price from (select CompanyPrices.*, Region from CompanyPrices inner join CompanyRegion on CompanyPrices.CompanyID=CompanyRegion.CompanyID) S1 inner join (select CompanyPrices.*, Region from CompanyPrices inner join CompanyRegion on CompanyPrices.CompanyID=CompanyRegion.CompanyID) S2 on S1.CompanyID = S2.CompanyID group by S1.CompanyID, S1.Price having count(*)<=10 order by S1.CompanyID, S1.Price
I am not sure what is wrong in the first query and why it does not work when the second one works. Could someone help in making the first query work to give me correct results?
I´m looking to create a select where I sum the daily_return by stock_code, and then I would like to have an additional column containing the most recent "rating" available by date
So if you where to execute the query below, the result would be:
stock1 0.54 3 stock2 0.05 1
Here is what I have so far:
DECLARE @stock_returns TABLE ( stock_code VARCHAR(10) NOT NULL, date1 DATE NOT NULL, daily_return NUMERIC(10, 2) NOT NULL, rating numeric (6,0) not null);
I have a table from cars GPS positions: CREATE TABLE Positions ( Ident VARCHAR(20) NOT NULL, Valid BOOL, Date DATETIME NOT NULL, Latitude FLOAT, Longitude FLOAT, Speed INT }
Sorry about the poor english... :) I like select the last position where Ident=DEMO1 and DEMO2.... How can I make that? Practically I sould like to now, where is the last positions of selected cars! I try this, but not good: SELECT MAX(date), ident, latitude, longitude, speed FROM positions WHERE valid=1 && (ident='DEMO1' || ident='DEMO2'|| ident='DEMO3') GROUP BY ident ;
I get 3 record, the dates is the last, but the lat. and long. is wrong...
