How do select the latest value

  • Thread starter Thread starter Tony
  • Start date Start date
T

Tony

In the database we have a column called grefnr and loopnr.
Each record could have something like the following se below. I want to
select the record with the highest loopnr number for every grefnr.
In this example I would get the following three record 1 78 and 2 95 and 3
99
How can I write this sql
grefnr loopnr
1 34
1 12
1 78
1 35
2 4
2 72
2 70
2 95
3 11
3 16
3 99
3 7

//Tony
 
In the database we have a column called grefnr and loopnr.
Each record could have something like the following se below. I want to
select the record with the highest loopnr number for every grefnr.
In this example I would get the following three record 1 78 and 2 95
and 3 99
How can I write this sql
Click to expand...

One way:

select grefnr, max(loopnr)
from Table_1
group by grefnr

Table_1 you need to replace with the name of the actual table.

// Anders
 
One way:

select grefnr, max(loopnr)
from Table_1
group by grefnr

Table_1 you need to replace with the name of the actual table.
Click to expand...

That is the way to do it if there are no more columns.

It is easily extended to handle the case where there are more columns
but the values are the same for each grefnr just by adding more columns
to the select list and the group by.

Things become tricky if there are more columns and they do not
have the sane values for each grefnr.

Arne
 
That is the way to do it if there are no more columns.

It is easily extended to handle the case where there are more columns
but the values are the same for each grefnr just by adding more columns
to the select list and the group by.

Things become tricky if there are more columns and they do not
have the sane values for each grefnr.

Arne
Click to expand...

Then

select t.* from Table_1 t
inner join (select grefnr, max(loopnr) as loopnr from Table_1 group by grefnr) as t1
on t1.grefnr = t.grefnr, t1.loopnr = t.loopnr

should work
 
Then

select t.* from Table_1 t
inner join (select grefnr, max(loopnr) as loopnr from Table_1 group by
grefnr) as t1
on t1.grefnr = t.grefnr, t1.loopnr = t.loopnr

should work
Click to expand...

I usually do that as:

select *
from table t1
where loopnr = (select max(loopnr) from table t2 where t2.grefnr=t1.grefnr)

but yours may be faster.

OP should of course be aware that they can return multiple
rows for the same grefnr.

But to only get one, then we wpuld need to know a criteria
for that (picking one randomly always sounds very suspiciously).

Arne
 
I usually do that as:

select *
from table t1
where loopnr = (select max(loopnr) from table t2 where t2.grefnr=t1.grefnr)

but yours may be faster.

OP should of course be aware that they can return multiple
rows for the same grefnr.

But to only get one, then we wpuld need to know a criteria
for that (picking one randomly always sounds very suspiciously).

Arne
Click to expand...

I would do that as well if grefnr were unique. My example assumes grefnr and loopnr combination is unique.
Of course the comma in the join criteria should be replaced with "and"

select t.* from Table_1 t
inner join (select grefnr, max(loopnr) as loopnr from Table_1 group by grefnr) as t1
on t1.grefnr = t.grefnr and t1.loopnr = t.loopnr
 
Back
Top