Record Select Question - 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

Record Select Question

Author

Topic

Chadzuk
Starting Member

6 Posts

Posted - 2008-08-21 : 12:01:13

I have a question on how to select a specific record based on criteria. Below is an example.

Record Code Amount
1 A 100
2 B 0
3 C 200
4 D 0

What I am trying to do is get the last record that has any amount in it so in the above example, I would want to select record 3. It could be where there is an amount in record 1 and 2 so I would want to display #2 or #2 and #4 and I would have to display #4.

Any assistance would be appreciated.

Thanks in advance!

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-08-21 : 12:07:57

This?

Select max(record)
from yourTable
where amount > 0

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-21 : 12:41:59

SELECT t.*
FROM YourTable t
INNER JOIN (SELECT MAX(Record) AS MaxRec
FROM YourTable
WHERE Amount>0)t1
ON t1.MaxRec=t.Record

Chadzuk
Starting Member

6 Posts

Posted - 2008-08-21 : 12:52:09

Not exactly. I'll try to do better to explain:

Given the example above I need to pull only the record that has the last amount in it but I need access to every field because I have to actually pull the code out.

If I do

Select Max(Record), Code, Amount
FROM My Table
WHERE Amount > 0
GROUP BY Code, Amount

It will show me

Record Code Amount
3 A 100
3 C 200

And all I want it to do is show me

Record Code Amount
3 C 200

Now, to make this more complex I could have the following:

Key Record Code Amount
12 1 A 100
12 2 B 0
12 3 C 200
12 4 D 0

13 1 A 150
13 2 B 150
13 3 C 0

As you can see now I have added a key field into it. I would need my query to display the following

Key Record Code Amount
12 3 C 200
13 2 B 150

Record 3 of the first Key would be the last to have an amount > 0 and Record 2 of Key 13 would display because it was the last record to have an amount > 0

I put a space in the new example just to make it easier to see what was happening.

Am I still not explaining it well enough.

Sorry, it can be a little complex and I may have to do it in multiple steps.

Chadzuk
Starting Member

6 Posts

Posted - 2008-08-21 : 13:38:04

Thanks visakh16, I'll work your query into my tables and see what I come up with.

Chadzuk
Starting Member

6 Posts

Posted - 2008-08-21 : 14:19:46

Hi visakh, your query got me a lot closer to what I was looking for. The only thing is it looks like it is disregarding the Amount>0. It's showing me the last record regardless of the balance but it is giving me the entire record correctly. Now if I could get it to give me the last record where the balance is > 0 it would be perfect. I'll try to toy with it a little bit. It may just be me being stupid.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-21 : 23:39:38

Is your Record value unique always?

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-08-22 : 05:18:39

Assuming record value is unique...

declare @t table ([Key] int, Record int, Code varchar(10), Amount int)
insert @t
          select 12, 1, 'A', 100
union all select 12, 2, 'B', 0
union all select 12, 3, 'C', 200
union all select 12, 4, 'D', 0
union all select 13, 1, 'A', 150
union all select 13, 2, 'B', 150
union all select 13, 3, 'C', 0

select a.* from @t a inner join (
    select [Key], max(Record) as Record from @t where Amount > 0 group by [Key]) b on a.[Key] = b.[Key] and a.Record = b.Record

If the record value isn't always unique, it would be useful to know what version of SQL you are using...

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

Chadzuk
Starting Member

6 Posts

Posted - 2008-08-26 : 09:55:43

My apologies all, I have not been around for a few days. I actually figured it out using a combination of what Visakh16 gave me and some other things. The issue was a lot more complex due to multiple table links and everything else however once I got by the initial hurdle of what I was explaning, I was able to get everything else to work. I used a temp table to store all of the records that had a balance, then from that temp table I got the MAX of "record" per key which showed me exactly what I was looking for.

The record value was always going to be unique per key(shown in the second example).

Seems to be working wonderfully now.

Thank you all for your excellent advice, it is much appreciated.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources