LastRecord function? - 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)

LastRecord function?

Author

Topic

passeggieri
Starting Member

5 Posts

Posted - 2004-02-17 : 04:48:47

Ciao! Heres the problem! I have a recordset

select v1.AirlineCode, v1.AgentCode, v1.PrepWeightChg
from vi_test v1
where awbnumber = '14667165'

XXX 1111113 9072.000000
XXX 1111111 .010000
XXX 1111111 -.010000
XXX 1111111 9072.000000

With the following query I do a group by:

select v1.AirlineCode, v1.AgentCode, Sum(PrepWeightChg)
from vi_test v1
where v1.awbnumber = '14667165'
group by v1.AirlineCode, v1.AgentCode

XXX 1111113 9072.000000
XXX 1111111 9072.000000

My problem is that I would need just one record, having as AgentCode only the last one: According that I always need the last AgentCode record, doesn't exist a sort of "LastRecord" function?

something like,

select v1.AirlineCode, LastRecord(v1.AgentCode), Sum(PrepWeightChg)
from vi_test v1
where v1.awbnumber = '14667165'
group by v1.AirlineCode

to obtain

XXX 1111111 18144.000000

thanks,

Filippo

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-02-17 : 05:28:33

There's no concept of first/last in SQL without ORDERing the data...
What makes you want to pick 1111111 instead of 1111113....a lower value?
or some other value you are not showing us...like a 'last recorded date'?

Once you answer this question, then the solution should become straighforward.

passeggieri
Starting Member

5 Posts

Posted - 2004-02-17 : 11:35:11

Unfortunately, this is a table I received and it doesn't have a date or something else even if I was told me the data are already ordered because they were inserted sequentially; I will try to obtain a date field or something like that!

thanks,

Filippo

nr
SQLTeam MVY

12543 Posts

Posted - 2004-02-17 : 11:49:13

>> even if I was told me the data are already ordered because they were inserted sequentially

Doesn't matter the data can be reorganised at any time - and in any case without an order by the data can be returned in a different order by the same query run twice.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-02-18 : 05:58:38

Even if you can't influence the order that data comes to you....why pick 1111111 instead of 1111113?

passeggieri
Starting Member

5 Posts

Posted - 2004-02-19 : 06:03:19

Ok, gentlemen/women, now I have some extra data in my table and I can assure you the examples below are right, I mean they are in the right order;

I need 1111111 instead of 1111113 because this is a correction that should replace the old value (1111113).

I still can't obtain one record with the aggregated field and the last record

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-02-19 : 07:28:17

[code]
...
where
not exists (
select 1
from
vi_test
where
v1.airline_code = airline_code and
v1.agentcode > agentcode)
...
[/code]

Something like that ...

Jay White
{0}

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-02-20 : 09:47:35

The vital piece of information your sql table is missing....is the relative order of the rows to each other.

By the sounds of it, you have a flat-file somewhere which is the source for your SQL table.
And having 1111111 after 1111113 means something important, to your application/database. In order to transfer that unwritten/undocumented knowledge into SQL so that it can be used in a query to solve your problem, you will have to ensure that each row in your SQL table has the equivalent of a "record id" on it....usually implemented by an identify field...but not necessarily.

Once that is there, then your problem becomes solveable.

FYI.
XXX 1111113 9072.000000
XXX 1111111 .010000
XXX 1111111 -.010000
XXX 1111111 9072.000000

and

XXX 1111111 .010000
XXX 1111113 9072.000000
XXX 1111111 -.010000
XXX 1111111 9072.000000

and

XXX 1111111 .010000
XXX 1111111 -.010000
XXX 1111113 9072.000000
XXX 1111111 9072.000000

are the same dataset in SQL

....and a "SELECT * FROM TABLE1" type query could at any point in time return (at random) any one of the 3 datasets above....even if run 2 million times in a row. Unless there is an ORDER BY phrase in the SELECT statement, the ORDER of the output dataset is NOT guaranteed.

passeggieri
Starting Member

5 Posts

Posted - 2004-02-20 : 11:43:06

Unfortunately, I think I didn't explain myself; Assuming that now I have a date field where I obtain this recordset:

XXX 1111113 9072.000000
XXX 1111111 .010000
XXX 1111111 -.010000
XXX 1111111 9072.000000

I have to obtain only one row:

XXX 1111111 18144.000000

where XXX is of course the same, 1111111 is the last row of the recordset (I am sure it is in this format because now I have an extra date field so I can use the ord4er by), and 18144.00 is an aggregated value (Sum); Now I can't find a suitable way to show the last row of the recordset (I naturally obtain, being an aggregated query, 2 rows, one with 1111113 and the other one with 1111111).

I hope to have been more clear now; however, thanks a lot for your support!!

Filippo

stannius
Starting Member

10 Posts

Posted - 2004-02-23 : 09:47:53

Like Page47 said - use the SELECT [TOP] 1 syntax

SELECT v.AgentCode,
(SELECT TOP 1 v1.AgentCode
FROM vi_test v1
WHERE v1.airline_code = airline_code AND
v1.awbnumber = '14667165'),
SUM(v.PrepWeightChg)
FROM
from vi_test v1
where v1.awbnumber = '14667165'
group by v1.AirlineCode, v1.AgentCode

it worked for me.

passeggieri
Starting Member

5 Posts

Posted - 2004-02-25 : 05:31:05

It works!!!! Now I see the point!
Thanks all for your support and patience!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources