How do select the latest value

[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

 

[Anders Eriksson]

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

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

 

[Arne Vajhøj]

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.

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

 

[alex]

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

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

 

[Arne Vajhøj]

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

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

 

[alex]

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