MS SQL Server :: Group By And Latest Record

Latest Record

Sep 20, 2007

My table are
Customer: customerId ,name
Order: orderId, customerId, product,date
I want to display latest order from the customer

View 2 Replies View Related

Latest Record For Particular ID

Jan 24, 2014

I have table has column called [last modified date] it has time too , i need to get the [latest modified date] for particular ID , there are many updates to the ID and i just need the latest date.

[PAX ID] | [Last Modified Date]
1 2013-01-02 18:23:00
1 2013-01-02 11:42:00
3 2013-01-01 09:00:00
3 2013-01-08 16:05:00
5 2013-01-01 09:09:00

View 2 Replies View Related

Get Record With Latest Date

Jun 4, 2007

if there are 2 records with different date how to write query for ----> get record with latest date

View 4 Replies View Related

How To Get Latest Date Record

Apr 10, 2014

I want to get latest updated date on each transid and only for status =approved .

-------------------
out put would be
------------
idtransid date status
31013/1/2014 approved
61031/2/2014 approved

table
-----------
idtransid date status
11011/1/2014 approved
21012/1/2014 close
31013/1/2014 approved
41021/2/2014 approved
51022/2/2014 close
61031/2/2014 approved

View 1 Replies View Related

Conditions On Latest Record

May 18, 2006

I have a table that has records layed out as so:Table:fd_Id INT IDENTITY (1, 1)fd_User VARCHAR(30)fd_Effective DATETIMEData could be as follows:1 | "user1" | 6/20/20012 | "user2" | 6/1/20023 | "user2" | 6/5/20024 | "user2" | 6/5/20025 | "user2" | 2/1/20026 | "user3" | 9/1/20037 | "user3" | 10/2/20028 | "user4" | 1/1/2005What I need to retrieve from that table is the SINGLE LATEST item ofeach fd_User.Results:1 | "user1" | 6/20/20013 | "user2" | 6/5/2002 (or 4 | "user2" | 6/5/2002) since the dates arethe same but only 1 of them6 | "user3" | 9/1/20038 | "user4" | 1/1/2005

View 3 Replies View Related

Get Record With Latest DateTime

Oct 1, 2007

View 1 Replies View Related

Query To Get Latest 2 Records For Each Group

Aug 21, 2014

select
DayRank = ROW_NUMBER() OVER(ORDER BY a.datedel DESC),
a.order,a.line,a.datedel,a.recpt,b.status,
b.item,b.t_sup
from historytbl a
inner join order b
on a.order = b.order
and a.line = b.line
and a.status =4
group by a.order,line,a.datedel,a.recpt,b.status,b.item,b.sup

The query is returned the results below.

Rank OrderLineDateDelrecptitemsup
----- -------------------------------
1aaa102014-18-08rc1zzz1231122
2bbb202014-08-08rc2zzz1231122
3ccc302014-04-08rc3zzz1231122
4ddd902014-08-11rc6yyy123333
5eee102014-05-11rc7yyy123333
5fff90 2014-02-11rc8yyy123333
6ggg102014-05-10rc9qqq123444
7hhh502014-04-10rc0qqq123444
8iii102014-04-10rc5rrr123555

However, I want to have the query only show most recent two records for each group of item and sup, please see the results I want below.

Rank OrderLineDateDelrecptitemsup
----- -------------------------------
1aaa102014-18-08rc1zzz1231122
2bbb202014-08-08rc2zzz1231122

4ddd902014-08-11rc6yyy123333
5eee102014-05-11rc7yyy123333

6ggg102014-05-10rc9qqq123444
7hhh502014-04-10rc0qqq123444

View 4 Replies View Related

Query To Fetch Latest Record

Jun 29, 2012

Table has details like below:

NAME UPATE-TIMESTAMP
==== ===============
mary time1
mary time2
mary time3
tom time1
tom time2
tom time3
tom time4
richard time1
richard time2

Output Expected:

NAME UPATE-TIMESTAMP
==== ===============
mary time3
tom time4
richard time2

In summary, the requirement is to fetch the latest upated record for each unique NAME.

View 1 Replies View Related

Latest Record For Each Reference Field

Feb 19, 2014

I have a table that contains a Ref field and a TransactionDate field. For each Ref field there are mutliple Transactions. I am trying to put together a query that selects the Latest Transaction date for each Ref field in the table.

I tried:

SELECT *
FROM tabMediaTransaction
WHERE (TransactionDate =
(SELECT MAX(TransactionDate) AS Expr1
FROM tabMediaTransaction AS tabMediaTransaction_1))

But this only returns 1 record for 1 Ref field. I need 1 record for each Ref field

View 7 Replies View Related

Unable To Get Latest Date Record?

Apr 10, 2014

i want to get latest updated date on each transid and only for status =approved .

-------------------
out put would be
------------
idtransid date status
31013/1/2014 approved
61031/2/2014 approved
table
-----------

idtransid date status
11011/1/2014 approved
21012/1/2014 close
31013/1/2014 approved
41021/2/2014 approved
51022/2/2014 close
61031/2/2014 approved

View 2 Replies View Related

Select Latest Records From GROUP BY Query

Feb 26, 2014

I have a table T (a1, ..., an, time, id). I need to select those rows that have different id (GROUP BY id), and from each "id group" the row that has the latest field 'time'. Something like SELECT a1, ..., an, time, id ORDER BY time DESC GROUP BY id. This is the wrong syntax and I don't know how to handle this.

View 3 Replies View Related

Query Problems - Group By And Latest Date

Mar 21, 2007

Hi all,

hopefully someone can suggest the best way of implementing the problem i am trying to resolve. We have a table which contains rows relating to tests run on our product. This table is populated from an SSIS job which parses CSV files.

There are multiple rows per serial number relating to multiple tests. The only tests i am interested in are the ones with an ID of T120. Here is the query i have so far which should make it a little easier to explain:

SELECT [SerialNumber]
,Param1
,[TimeStamp]
FROM [Build Efficiency System].[dbo].[SSIS_SCANNERDATA_TBL]
WHERE Test = 'T120'
GROUP BY SerialNumber, Param1, [TimeStamp]
ORDER BY SerialNumber

What i have above is fine to a point. The problem i am encountering is that in test T120 it specifies a part which can be be one of about 6 in field Param1. If during testing there is a problem with the part then it is replaced and the test run a second time up until the whole product passes the test. The query above returns all instances of replacements so i may have the out put as follows:

SerialNumber Param1 TimeStamp
0 Part1 15/03/07
0 Part2 15/03/07
0 Part2 16/03/07
0 Part3 15/03/03

What i really need is to only list the last part that is installed, hence the one with the latest timestamp:

SerialNumber Param1 TimeStamp

0 Part1 15/03/07

0 Part2 16/03/07

0 Part3 15/03/03

Can someone please help me to alter the above query so that it will show only those Param1 fields that have the latest date for each part.

Many thanks in advance,

Grant

View 8 Replies View Related

T-SQL (SS2K8) :: Selecting Latest Record With Info

Aug 27, 2014

I have created the following SQL snippet that is a very simple mock-up illustrating the problem (I hope!) that I am facing:

-- create table
if object_id('tempdb..#tmpdelnotes') is not null
drop table #tmpdelnotes

create table #tmpdelnotes(
DelNote int identity (1,1) ,
DelDate date not null,
Item int not null,
Customer int not null)

[code]...

What I need to retrieve is a unique list of item numbers with information about the latest (DelDate) delivery note. The "Clumsy workaround" works, but is not very pretty when doing multiple table joins. Is it really necessary to use a derived table for this kind of query? Window functions can only exist in the SELECT and ORDER BY clauses, which is understandable since the calculations take place (I would guess) after the aggregations in the HAVING clause.

View 2 Replies View Related

SQL Server 2008 :: Query To Group And Find Latest

Aug 25, 2015

I have a scenario as below for one ID -

+------+--------+----------------------------+-------+
| id | amount | date | descr|
+------+--------+-----------------------------+------+
| 5689 | 10.00 | 2015-08-25 12:10:57.107 | 4 |
| 5689 | 10.00 | 2015-08-24 12:07:57.107 | 3 |
| 5689 | 10.00 | 2015-08-25 12:05:57.107 | 3 |
| 5689 | 130.00 | 2015-08-24 12:07:57.107 | 4 |
| 5689 | 130.00 | 2015-08-25 12:07:57.107 | 3 |
+------+--------+-----------------------------+-----+

I want to fetch below 3 records from the above scenario i.e. latest record of each amount (Latest is determined using "descr" column i.e. 4 is greater then 3 -

+------+--------+----------------------------+-------+
| id | amount | date | descr|
+------+--------+-----------------------------+------+
| 5689 | 10.00 | 2015-08-25 12:10:57.107 | 4 |
| 5689 | 10.00 | 2015-08-24 12:07:57.107 | 3 |
| 5689 | 130.00 | 2015-08-24 12:07:57.107 | 4 |
+------+--------+-----------------------------+-----+

But in case of same amounts I am unable to fetch the latest status as even using partitioning will treat them as one.

CREATE TABLE #TMP
(
ID INT,
AMOUNT DECIMAL,
[DATE] DATETIME,
DESCR VARCHAR(10)
)

INSERT INTO #TMP VALUES
(5689,10.00,'2015-08-25 12:10:57.107','4')
,(5689,10.00,'2015-08-24 12:07:57.107','3')
,(5689,10.00,'2015-08-25 12:05:57.107','3')
,(5689,130.00,'2015-08-24 12:07:57.107','4')
,(5689,130.00,'2015-08-25 12:07:57.107','3')

View 8 Replies View Related

Transact SQL :: Retrieve Latest Record Of Serial Number With Multiple Entries

Sep 30, 2015

I work for an organization that repairs serialized devices. Each time a device is repaired it's serial number is recorded in a database table along with the date it was repaired along with other information about the device. There are multiple cases where a unit has been repaired more than once.

I am trying to write a query that will return the serial only once and that record will be the record of the latest repair date. To sum it up,

Return a list of serials where if a serial exists more than once in the table, return only the instance of the serial record(s) with the max(created_dt). The end result will be a list of distinct serial numbers.

Here is my Query. The problem I believe is in my sub-query but I am not sure how to structure it.

SELECT
S.Id
, RMA
, PinSerial
, L4Serial
, L4Model

[Code] ....

View 3 Replies View Related

Getting First Record Of Each Group

May 21, 2002

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?

View 1 Replies View Related

Group Min Record

Sep 16, 2004

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

View 1 Replies View Related

Getting Group-by By Record Value In Second Row?

Dec 14, 2014

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

View 1 Replies View Related

How Can I Get The First Record Of Each Group

Feb 19, 2006

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"

View 2 Replies View Related

Last Record In Each Group

Feb 28, 2007

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

View 1 Replies View Related

Select Last Record By Group?

May 18, 2012

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

View 4 Replies View Related

SQL Select Last Record Per Group

Jan 24, 2004

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

View 2 Replies View Related

Select One Record From Each Group

Jan 21, 2014

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 Related

Selecting Top Record In A Group?

Mar 18, 2015

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.

View 4 Replies View Related

Get First Record From Group By Query

May 30, 2006

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_"

View 1 Replies View Related

SQL Record Selectons By Group

Jun 22, 2007

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.

View 4 Replies View Related

Top 1 Record Of A Group Of Records

Aug 14, 2007

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.

View 8 Replies View Related

How To Select Only The First Record From A Group

Sep 18, 2006

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.

Thanks,

BobD

View 2 Replies View Related

Update First Row Of A Group Of Record

Jun 12, 2006

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.

View 1 Replies View Related

T-SQL (SS2K8) :: Display Record Using Group By?

Aug 3, 2015

I 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

View 3 Replies View Related

Retrieve When All Values In Group Have Same Record

Feb 25, 2015

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?

View 2 Replies View Related

Select The Last Record Group By? Ident....

May 13, 2007

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

View 5 Replies View Related