Get last record - 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

General SQL Server Forums

New to SQL Server Programming

Get last record

Author

Topic

scarr
Starting Member

4 Posts

Posted - 2010-07-15 : 06:50:20

Hi all,

New'ish to SQL, well anything more than a basic SELECT statement anyway :)

Here is my current SQL statement:

SELECT top 1 tblgps.iunitid,tblgps.dtGPS from tblgps
where tblgps.iunitID IN (select tblunits.iunitid from dbo.tblunits
where tblunits.iaccountid = 75)
order by tblgps.dtGPS desc

I want to get the last record inserted into tblGPS for each unit, ths IN function is returning the list of UNITS OK and this statement does return 1 record for a unit, I want 1 record for each unit and that record being the last one inserted into tblGPS.

I understand why this does statement does not work but I cannot figure out what statement will!

Help..... and thanks in advance.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-15 : 07:01:52

Please tell us your sql server version and give us table structure, sample data and the wanted output in relation to the sample data.

No, you're never too old to Yak'n'Roll if you're too young to die.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-07-15 : 07:02:39

If you are using sql server 2005, then try the below select statement.

Select iunitid, dtGPS From (
Select Rank() over (Partition by iunitid order by dtGPS desc) as Srno, tblgps.iunitid, tblgps.dtGPS from tblgps tg
inner join dbo.tblunits tu on tg.iunitID = tu.iunitID and tu.iaccountid = 75
) as SubTab
Where SubTab.Srno = 1

If you are using SQL 2000 then us know.

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-15 : 07:19:05

Hi Bohra,
better use row_number() instead of rank().
Why?
Execute this and see:
select
row_number() over (partition by id order by datecol desc) as rownum,
rank() over (partition by id order by datecol desc) as ranknum,
*
from
(
select 1 as id,'20100714' as datecol union all
select 1 as id,'20100714' as datecol union all
select 1 as id,'20100715' as datecol union all
select 1 as id,'20100715' as datecol
)dt

No, you're never too old to Yak'n'Roll if you're too young to die.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-07-15 : 07:43:40

quote:
Originally posted by webfred

Hi Bohra,
better use row_number() instead of rank().
Why?
Execute this and see:
select
row_number() over (partition by id order by datecol desc) as rownum,
rank() over (partition by id order by datecol desc) as ranknum,
*
from
(
select 1 as id,'20100714' as datecol union all
select 1 as id,'20100714' as datecol union all
select 1 as id,'20100715' as datecol union all
select 1 as id,'20100715' as datecol
)dt

No, you're never too old to Yak'n'Roll if you're too young to die.

Hi Webfred,

I have given solution earlier using row_number() but don't know how i missed this time.

Will remember it in future.

Thanks Webfred for the correction.

Thanks.

scarr
Starting Member

4 Posts

Posted - 2010-07-15 : 11:20:39

Hi,

first of all I'm using SQL 2005, second not sure how best to give you structure is there a report or output from SQL2005 that will give you the information you require? also the same for example data, I did try cut and paste from a simple select statement on the tblGPS but once dropped in here it was totally unformatted and unreadable.

Thanks and sorry for being a total newbie!

Steve

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-15 : 12:14:36

Make it easy!
Just tell us something like this:

I have a table_a with columns (not all, only the needed to understand...)
Id int,
insertDate datetime,
...

another table_b with columns
Id int,
table_a_ID int,
...

Data
table_a (Id, insertDate)
1, '20100710'
2, '20100630'

table_b (Id, table_a_ID)
1, 1
2, 1

and so on.

And then in relation to that sample data the wanted output is
...

No, you're never too old to Yak'n'Roll if you're too young to die.

Kristen
Test

22859 Posts

Posted - 2010-07-15 : 12:47:29

Even better

would be a little worked example:


DECLARE @MyData TABLE
(
    Id int,
    insertDate datetime
)

INSERT INTO @MyDAta
SELECT 1, '20100710' UNION ALL
SELECT 2, '20100630' UNION ALL
SELECT 3, '20100525'

then each person who tries to provide a solution doesn't need to do all that typing (in duplicate with each other person answering ...) just to get to the point where they can provide an answer ...

and ... usually .... more people will provide solutions as a result

and often the process is enough for the Author to work out what the solutions is too

Kristen
Test

22859 Posts

Posted - 2010-07-15 : 12:48:46

"I did try cut and paste from a simple select statement on the tblGPS but once dropped in here it was totally unformatted and unreadable."

Put

[code]
... your code here
[/code]

"tags" around it to preserve the formatting.

scarr
Starting Member

4 Posts

Posted - 2010-07-15 : 15:26:03

Thanks everyone,

I'm very impressed with A) response and B) not calling me an idiot :)

OK here we go

First let me say I did not create any of these tables / setup the database or claim to know much about SQL the following line is about my level of knowledge when it comes to SQL

select [fields] from [table] where [criteria] order by [direction] with a few added bits like TOP and anything I can read on web. I do understand VB and VBA though not that this helps.

tblUnits (table)
iUnitID (int)
iAccountID(int)

tblGPS (table)
dtGPS (datetime)

The two table are linked by iunitID, tblunits is a table with all units, all have assigned to them a account ID (iAccountID), I am only intrested in units with iaccountID = 75, the tblGPS has millions of records.

Q. I want to get the most resent record for each unit, this can be determined by dtGPS

iUnitID dtGPS
84 6/7/2010 15:28:00
101 6/7/2010 17:52:00
54 6/7/2010 11:54:00
13 6/6/2010 01:52:00

Steve

P.s. I hope my description is OK but forgive me if it is not up to scratch.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-15 : 16:18:02

With million of rows I wouldn't use the solution with row_number().
Try this:
select
tblU.iunitID,
max(tblG.dtGPS) as dtGPS
from tblUnits as tblU
join tblGPS as tblG
on tblU.iAccountID=75 and tblU.iunitID = tblG.iunitID
group by tblU.iunitID

No, you're never too old to Yak'n'Roll if you're too young to die.

Kristen
Test

22859 Posts

Posted - 2010-07-15 : 18:22:28

"With million of rows I wouldn't use the solution with row_number()."

OK, I was curious, so I tried it

SELECT COUNT(*), COUNT(DISTINCT pkCol1) FROM dbo.MyOrderItems

Gave 4,109,227 and 628,961


CREATE TABLE #MyTable
(
	pkCol1 int,
	Col3   varchar(30),
	PRIMARY KEY
	(
		pkCol1
	)
)

INSERT INTO #MyTable
SELECT	pkCol1,
	Col3
FROM
(
	SELECT	[T_RowNumber] = ROW_NUMBER()
			OVER
			(
				PARTITION BY pkCol1
				ORDER BY pkCol1, pkCol2 DESC
			),
		pkCol1,
		Col3
	FROM	dbo.MyOrderItems
) AS T
WHERE	T_RowNumber = 1
ORDER BY pkCol1

10 seconds to insert 628,961 rows

Changing ORDER BY pkCol1, pkCol2 DESC to be ascending (which matches the PK) it then took 4 seconds


INSERT INTO #KBM_TEMP
SELECT	OI.pkCol1,
	OI.Col3
FROM
(
	SELECT	pkCol1,
		[pkCol2_MAX] = MAX(pkCol2)
	FROM	dbo.MyOrderItems
	GROUP BY pkCol1
) AS T
	JOIN dbo.MyOrderItems AS OI
		 ON OI.pkCol1= T.pkCol1
		AND OI.pkCol2 = T.pkCol2_MAX
ORDER BY OI.pkCol1

took 5 seconds, changing to use MIN (first matching item for each pkCol1 in PK) took 3 seconds

Query Plans:


DESC

|--Parallelism(Gather Streams, ORDER BY:([MyDB].[dbo].[MyOrderItems].[pkCol1] ASC))
  |--Filter(WHERE:([Expr1003]=(1)))
    |--Parallelism(Distribute Streams, RoundRobin Partitioning)
      |--Sequence Project(DEFINE:([Expr1003]=row_number))
        |--Segment
          |--Parallelism(Gather Streams, ORDER BY:([MyDB].[dbo].[MyOrderItems].[pkCol1] ASC, [MyDB].[dbo].[MyOrderItems].[pkCol2] DESC))
            |--Sort(ORDER BY:([MyDB].[dbo].[MyOrderItems].[pkCol1] ASC, [MyDB].[dbo].[MyOrderItems].[pkCol2] DESC))
              |--Clustered Index Scan(OBJECT:([MyDB].[dbo].[MyOrderItems].[PK_MyOrderItems]))

Table 'MyOrderItems'. Scan count 5, logical reads 125585


ASC

|--Filter(WHERE:([Expr1003]=(1)))
  |--Sequence Project(DEFINE:([Expr1003]=row_number))
    |--Segment
      |--Clustered Index Scan(OBJECT:([MyDB].[dbo].[MyOrderItems].[PK_MyOrderItems]), ORDERED FORWARD)

Table 'MyOrderItems'. Scan count 1, logical reads 124823


MAX

|--Parallelism(Gather Streams, ORDER BY:([OI].[pkCol1] ASC))
  |--Sort(ORDER BY:([OI].[pkCol1] ASC))
    |--Top(TOP EXPRESSION:((1)))
      |--Segment
        |--Parallelism(Distribute Streams, Hash Partitioning, PARTITION COLUMNS:([OI].[pkCol1]))
          |--Clustered Index Scan(OBJECT:([MyDB].[dbo].[MyOrderItems].[PK_MyOrderItems] AS [OI]), ORDERED BACKWARD)

Table 'MyOrderItems'. Scan count 1, logical reads 124823
Table 'Worktable'. Scan count 0, logical reads 0


MIN

|--Top(TOP EXPRESSION:((1)))
  |--Segment
    |--Clustered Index Scan(OBJECT:([MyDB].[dbo].[MyOrderItems].[PK_MyOrderItems] AS [OI]), ORDERED FORWARD)

Table 'MyOrderItems'. Scan count 1, logical reads 124823

(Live server, number of rows may have increased by a couple of dozen during tests)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources