query to show last record/Partner or PartnerName.
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.
TABLE1 info
ID1 fname1 lname1 DOB1
ID2 fname2 lname2 DOB2
ID3 fname3 lname3 DOB3
TABLE2 info
ID1 Visit2
ID1 Visit3
ID1 Visit1
ID1 Visit4
ID2 Visit1
ID2 Visit2
ID3 Visit1
I need a view or SP to return the following:
ID1 fname1 lname1 dob1 visit4
ID2 fname2 lname2 dob2 visit2
ID3 fname3 lname3 dob3 visit1
It seems like it should be a smiple process, only I just can't get the syntax to work.... Any Help would be GREAT!
thx
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?
View 1 Replies View RelatedI 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.
Thanks,
BobD
Hello
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...
Thanks,
Laci
Is there a way to do this.
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?
Hello Guyz,
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
Any help would be very much appreciated..
Cyherus
I have following query
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
which gives following result:
**Date Item_Code In_Quantity Issue_Quanitity BalanceQty**
2014-12-02 pen 100 0 100
2014-12-03 pen 50 50 0
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
Master,
How can I get the first record of each group which I had selected by "Group by".I try it but failed.3ks
"USE pubs
SELECT top 1 *
FROM titles
GROUP BY type
ORDER BY price"
Greetings anyone -
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.
Help pls?
Tks & B/R
Here's what I'm trying to do
TransIDMemberIDFundIDBuyPriceDate
1501101$1042013-01-24
2501102$1012013-04-23
3501102$1182013-02-04
4501102$982013-05-19
5501103$532013-05-21
6501103$392013-07-09
I'm trying to get the latest buy price for each fund a member bought.
So in the above example, I'm trying to formulate a query to retrieve all the latest bought price for memberid 105:
Desire Query Result
TransIDMemberIDFundIDBuyPriceDate
1501101$1042013-01-24
4501102$982013-05-19
6501103$392013-07-09
So far, I've tried using Group By clause on FundID and BuyPrice and MAX() function for the date, but I can' get the TransID.
I’m writing a document management system. The documents themselves are created from the contents of a database. The database is SQL Server.
The database contains a table, like so:
ClientID, ProjectID, DocumentID, MinorVersion, MajorVersion, Name
Initially, the user will create a “V0.1” document. So the data would look something like
ClientID = 1
ProjectID = 1
DocumentID = 1
MajorVersion = 0
MinorVersion = 1
Name = “My Document”
Thereafter, the user can create new versions as “0.2”, “0.3”, etc., or “1.0”, “1.1”, “2.0”, etc.
For example, a “2.1” document would be stored as:
ClientID = 1
ProjectID = 1
DocumentID = 1
MajorVersion = 2
MinorVersion = 1
Name = “My Document”
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
_Hendra_"
I have a set of timecard records with a projectNumber, taskNumber and TimeCardHours:
Rec# projectNumbeer taskNumber Employee timeCardHours
1 123 1.01.Test1 John 4
2 123 1.01.Test1 John 6
3 123 1.99.Test3 Mary 8
4 123 1.02.Test5 Mary 4
5 123 1.06.Test6 Mary 4
6 123 1.08.xyz5 Mary 6
7 123 1.03.xyz7 Jane 8
8 123 1.01.xyz4 Jane 5
9 123 1.04.xyz7 Jane 3
10 123 1.05.Test6 Mary 4
11 123 1.07.Test6 Mary 4
I have the following fixed set of tasks:
taskID taskNumberPrefix Task Description phaseID
11.01 Planning1
21.02 Design2
31.03 Requirements2
41.04 Coding3
51.05 Testing4
61.06 Build3
71.07 Packaging3
81.08 Documentation5
91.99 Management1
Finally, I have different phases:
1Planning
2Analysis
3Development
4Testing
5Documentation
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%.
Thanks for any assistance.
Hi all, I am relatively new to sql and have an issue I am trying to solve. I have a table with several records with the same id:
id amount date
-- ------ ------
1 100 01/01/2006
1 2000 06/01/2005
2 200 01/01/2005
2 500 06/01/2007
how would I get the data for the record with the newest date?
So I would return:
id amount date
-- ------ ----------
1 100 01/01/2006
2 500 06/01/2007
Thanks in advance for the help.
I have a table as shows here:
col1 col2
1 rec1 *
2 rec1
3 rec1
4 rec2 *
5 rec2
6 rec2
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?
thanks a lot.
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
View 2 Replies View RelatedI would like to display all the products with maximum SeqNo from the table below:
TABLE
ProductIDSeqNoBalance
111215
11135
111420
111510
12115
1212100
121325
121445
OUTPUT
ProductIDSeqNoBalance
111510
121445
Here is some sample data:
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?
How to get
Code Block
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.
Is there a way to do that?
Thanks!
I have a table with records like that.
Group | Value
Team 1 | 0
Team 1 | 0
Team 1 | 1
Team 1 | 1
Team 2 | 0
Team 2 | 0
Team 2 | 0
I want a script that return 0 if all the values of the group are 0 and return 1 if the records of the value is mixed with 0 and 1.
This my table named myData
CREATE TABLE [dbo].[myData](
[idx] [int] NULL,
[paymentMethod] [nvarchar](200) NULL,
[daerahKutipan] [int] NULL,
[payer] [int] NULL,
[code]....
I want to calculate the number of payer Group By paymentMethod. The number of payer must be divided by daerahKutipan. So far, my query as follow
select paymentMethod,
COUNT(CASE WHEN daerahKutipan = 1 THEN payer ELSE 0 END) figure_Seremban,
COUNT(CASE WHEN daerahKutipan = 3 THEN payer ELSE 0 END) figure_KualaPilah,
COUNT(CASE WHEN daerahKutipan = 4 THEN payer ELSE 0 END) figure_PortDickson,
COUNT(CASE WHEN daerahKutipan = 5 THEN payer ELSE 0 END) figure_Jelebu,
COUNT(CASE WHEN daerahKutipan = 6 THEN payer ELSE 0 END) figure_Tampin,
COUNT(CASE WHEN daerahKutipan = 7 THEN payer ELSE 0 END) figure_Rembau,
[code]....
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.
I would like to exec a select statement in VB/C++ to return first 100 records? What is the SQL statement should be?
Thanks,
Sam
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.
View 4 Replies View RelatedHello, everyone:
There is a table as below that has three groups regarding to cus. How to select first cid for each group. Thanks.
zyt
cus cid
ALFKI10643
ALFKI10692
ALFKI10702
ALFKI10835
ALFKI10952
ALFKI11011
ANATR10308
ANATR10625
ANATR10759
ANATR10926
ANTON10365
ANTON10507
ANTON10535
ANTON10573
ANTON10677
ANTON10682
ANTON10856
AROUT10355
AROUT10383
AROUT10453
AROUT10558
AROUT10707
AROUT10741
AROUT10743
AROUT10768
AROUT10793
AROUT10864
AROUT10920
AROUT10953
AROUT11016
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?
create table #tmpSched (
sched_date datetime,
product char(3)
)
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
View 1 Replies View RelatedHi, on a table with two fields DATETIME and PLATAFORM I need to meka thefollowing queries
One return the total grouped by hour like
'1:00:00.000' - 5 /* Registries from 0:00:00.000 to 1:00:00.000 from the day choose */
'2:00:00.000' - 12/* Registries from 1:00:00.001 to 2:00:00.000 from the day choose */
'3:00:00.000' - 5/* Registries from 2:00:00.001 to 3:00:00.000 from the day choose */
'4:00:00.000' - 12/* Registries from 3:00:00.001 to 4:00:00.000 from the day choose */
and on....
the other is group by week, month(with the 30, 31 days of the month
Like
01/05/2006 -- 5
02/05/2006 -- 5
03/05/2006 -- 5
and year(with the 12 months)
january - 5
february -- 12
What the easiest and best way to do that...
I tried with cursor but it takes too much time to complete.