last record in group - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

last record in group

Author

Topic

deadfish
Starting Member

38 Posts

Posted - 2003-04-02 : 21:53:46

How to select the last inserted record in groups like:

Employee Projects
------------------
E1 P1
E1 P3
E1 P2
E2 P2
E2 P1

should return:

Employee Prjects
----------------
E1 P2
E2 P1

Thanks!

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2003-04-02 : 22:08:59

Is there any field in this table to indicate when it was entered, or even an identity field? Without something like this, you can't tell which record was entered last.
If you had such a field, you could do the following:

SELECT Employee, Projects
FROM Table 
WHERE Seq IN (SELECT Max(Seq) 
              FROM Table
              WHERE Employee IN (SELECT DISTINCT Employee FROM Table))

, where Seq is the sequencing field.

The query's a bit long-winded; there may be a nicer way of doing this....

deadfish
Starting Member

38 Posts

Posted - 2003-04-02 : 22:13:51

Thanks Timmy!

The main problem is that I don't have any field indicating the last inserted record except the record number...

Is it possible to find out the last inserted record using the record number in the sql table ??

Edited by - deadfish on 04/02/2003 22:14:48

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-04-03 : 07:10:46

It depends what you mean by "record number".

Jay White
{0}

SamC
White Water Yakist

3467 Posts

Posted - 2003-04-03 : 08:46:24

There doesn't seem to be any record identifier in your post that tells us any order.

Can you post the columns, including the identity column (if any)

Sam

----------------------
I have made this a rather long letter because I haven't had time to make it shorter.
- Blaise Pascal, 1623 - 1662

enrique
Starting Member

7 Posts

Posted - 2003-09-24 : 15:01:37

I am having the same problem that deadfish had and I am doing what you have proposed an it is not working. My case is the next.

Invoice_number Project Sequence
000005 0323359 165715075
888891 2223160 100457663
888892 2223160 101417963
888893 0323359 154053136
888893 0323359 154323154
888894 0323425 174149309
888894 0323425 174238090
888895 0323363 174301371
888895 0323363 174314167
I would like to get.
888892 2223160 101417963
888893 0323359 154323154
888894 0323425 174238090
888895 0323363 174314167

but it is not working my script is

SELECT Invoice_number, project, sequence
FROM table
WHERE sequence IN(SELECT MAX(sequence)
FROM table
WHERE project IN (SELECT DISTINCT project
FROM table))

Any help would be appreciated.

Thanks

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2003-09-24 : 15:49:03

You have to Link the Max(Sequence) value to the Project itself to pick out the row desired.

Following the logic of the query you wrote:

It selects the distinct project numbers, then calculates the max(sequence) number of all rows that have those project numbers (in your sample data that would be 174314167) and returns all rows with with that sequence number.

See if this works:

SELECT	a.Invoice_number, a.Project, a.Sequence
FROM	table a JOIN
	(
	SELECT	Project, max(Sequence) Sequence
	FROM	table
	GROUP BY Project
	) b
ON
	a.Project = b.Project
AND	
	a.Sequence = b.Sequence

enrique
Starting Member

7 Posts

Posted - 2003-09-24 : 16:54:57

drymchaser:

It worked, I owe you a beer whenever you were in Toronto send my an email.

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources