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.
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?
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...
If I have a sorting which gives me multiples of column C1 but which can be uniquely identified by C1+C2+C3, and I want to query for the first C1 in each of C1's unique values, how would I go about doing that?
A small problem here, I have the below table and I need to group and display the record that has the minimum value in the table (this table is derived from a query that permutates some records to give me this result).
F1 F2 F3 QQQ C 2 QQQ B 1 QQQ A 3
expected result: QQQ B 1
my result: when I group by F1, First(F2) and MIN(F3): QQQ C 1
when I group by F1, MIN(F2) and MIN(F3): QQQ A 1
when I group by F1, F2 and MIN(F3): QQQ C 2 QQQ B 1 QQQ A 3
Select Date, Item_Code, SUM(In_Quantity) as In_Quantity, SUM(Issue_Quantity) as Issue_Quantity, (SUM(In_Quantity)-SUM(issue_Quantity)) as BalanceQty from
( select tbl_add_product.Date as Date, tbl_add_product.Item_Code, tbl_add_product.In_Quantity, 0 as Issue_Quantity from tbl_add_product where Item_Code = 'pen' union ALL select tbl_issue_product.Date as Date, tbl_issue_product.Item_Code, 0 as In_Quantity, Issue_Quantity from tbl_issue_product where Item_Code = 'pen' ) X group by Item_Code, Date
I want to 100 in second row. the logic is that balance Qty from first row should be added to In_Qty so that when Issue_Quantity is subtracted from it, it gives BalanceQty
I have been attempting to figure out - in Report Builder - how to print only the last record in each group, i.e. the last activity. I see no Last function available anywhere.
Would someone please be so kind as to advise how this can be done?
If this cannot be accomplished in Rpt Builder, it looks as if the Last function is available within Report Designer. Unfortunately, although documentation states it's available, it does not state where this function can be utilized. I'm certainly having no luck.
The earlier versions will still exist on the database, but the latest version will be 2.1.
There may be several different documents, with different DocumentID’s (e.g. DocumentID = “1”, DocumentID = “2”), etc., and each of these documents may have many versions.
I’m trying to write a query to display a list of documents showing ClientID, ProjectID, DocumentID, MinorVersion, MajorVersion, Name… but the list should only display the latest version of each document.
So, if the database contained the following records:
ClientID, ProjectID, DocumentID, MajorVersion, MinorVersion, Name 1,1,1,0,1,My Document 1,1,1,0,2,My Document 1,1,1,0,3,My Document 1,1,1,1,0,My Document 1,1,1,2,0,My Document 1,1,1,2,1,My Document 1,1,2,0,1,My Second Document 1,1,2,0,2,My Second Document 1,1,2,0,3,My Second Document
My query should return:
ClientID, ProjectID, DocumentID, MajorVersion, MinorVersion, Name 1,1,1,2,1,My Document 1,1,2,0,3,My Document
… where 2.1 is the latest version of Document 1 and 0.3 is the latest version of Document 2.how to do it.
Hendra writes "I'd like to know is there any way or any statement that can help me to get first or last record from the group by query ? I'm using sql Server 2000 Thank's
What I would like to do is obtain a summary of timecard hours for each phase.
So for example, in Phase 1, Planning, this has two tasks based on PhaseID=1, Planning and Management which have task number prefixes of 1.01 and 1.99 respectively.
What I would like to is sum all the timecard records are LIKE 1.01% or 1.99% so my final results based on the above data would provide the phase name and total hours.
Planning Phase total Hours Planning 23 (Sum of records 1,2,3,8) Analysis 12 (Sum of records 4,7) Coding 8 (Sum of records 5,9,11) Testing 4 (Sum of record 10) Documentation 6 (Sum of record 6)
I have tried various select statements, but helps up when I want to have TaskNumberPrefix LIKE taskNumber%.
and now I need to update the field col1 of the first records with different col2 value. that means I need to update the col1 filed of the * marked record.
any suggestion about how to do it with SQL server 2000?
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
CREATE TABLE #MyTable ( Pk INT, GroupID INT, Value VARCHAR(10)
[code]...
I am looking to retrieve any GroupID in which every Value of that GroupID is either (a) null, (b) an empty string, or (c) "XYZ". So in the above example, GroupID #1 would not be returned in my query because there is a Value of "ABC", but GroupID #2 would be returned since it consists of only nulls, "XYZ"'s, and empty strings.What would be the most efficient way to write such a query?
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.
Looking to see if thier is a better way to find the last record entered in a group of records.
What I'm doing now is finding the max for the secound column and then doing a sub query to find the max of the third column having the second columns equal.
Table example using simplied data.
PolId
CoveragId
EffDate
Status
Limit1
2
1
9/7/2007
a
10000
2
2
9/7/2007
a
150000
2
2
10/1/2007
a
200000
3
1
9/7/2007
a
10000
The parent program addes a row every time the data is changed. To make things worst; the records arn't always in sqenal order like the above table and some time edits the row instead.
The current query returns a single value. from a single table.
Current code used in the select protion on a larger query. bpi = basicpolicyInformation.
( Select c1.limit1 From AFW_Coverage as c1 Where c1.PolId=bpi.PolId and c1.CoverageId = (select max(CoverageId) as CoverageId From AFW_Coverage as c where c.PolId = c1.PolId and c.CoverageCode = 'Dwelling' and status <> 'D' ) and c1.effDate = (select max(Effdate) as Effdate From AFW_Coverage as c where c.PolId = c1.PolId and c.CoverageID = c1.CoverageId )
Explain the current code. It uses the two sub queries to find the correct record ID that has the data needed.
I have a table where I am grouping on one field and would like an individual (separate) count of values from another field of same table. So for example, I have following data:
instance_id, area, values 101 North 1 102 North 2 103 East 2 104 East 2
I would like to report on Area, and count of rows with different Values types, for example:
Area Value - 1, Value - 2, Value - 3 North 1 1 0 East 0 2 0
I am not sure what the technical term is for such report, but I can group by Area column, and but its aggregating counts on different Value types that I am having difficulty in performing in SSRS.
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
Hello! 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
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
