How to get one record from a group of records

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)

How to get one record from a group of records

Author

Topic

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2003-03-04 : 12:58:44

There should be a way to do this without using Group By, isn't there?

The Table consists of:

ClientCode Trx_Date ErrorCode ErrorLevel
TEST1 1/2/03 100 100
TEST1 3/2/03 50 50
TEST1 3/4/03 20 20
TEST2 1/5/03 100 100
TEST2 2/7/03 200 200

How can I get a result set of...
TEST1 3/4/03 20 20
TEST2 2/7/03 200 200

without using "Group By". This is just an example. The SPROC I am writing has alot more to it, building a runtime select statement using with 5 different JOINS. But the basis I am looking for is how to get 1 record from a group of records without using Group By, so I don't have to worry about error 8120 (setting up MAX(), etc.)

Thanks!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-03-04 : 13:06:07

What are you trying to get? The latest transaction for each TEST? It definitely helps if you let us know instead of us trying to reverse engineer your alogorithm based on sample output.

If that is what you would like, try:

SELECT A.* FROM YourTable A
WHERE
Trx_Date = (SELECT Max(Trx_Date) FROM YourTable B WHERE A.ClientCode = B.ClientCode)

- Jeff

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2003-03-04 : 14:10:54

Thanks Jeff.

Yes, I want to get the last transaction for each client code. In the table example there are 2 client codes, TEST1, and TEST2 each having multiple transactions. I see how your SQL Select would work. What if there are more than one record for the same last date?...

IDKey Client Date Value
20001 TEST1 1/2/03 100
20034 TEST1 3/2/03 50
20045 TEST1 3/4/03 20
20002 TEST1 3/4/03 10

20345 TEST2 1/5/03 100
20346 TEST2 2/7/03 200
20347 TEST2 2/7/03 300

Then how can I get the result set of..
TEST1 3/4/03 10
TEST2 2/7/03 300

I do have an Uniquie IDKEY field setup for each record which is a SQL Server generated ID.

Thanks a bunch!
Ken

ojn.
Starting Member

10 Posts

Posted - 2003-03-05 : 16:47:29

You can do this...

select *
from tb t1
where IDKEY in(select top 1 IDKEY
from tb t2
where t2.client=t1.client
order by [date] desc)

--
-oj
www.rac4sql.net

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-03-05 : 17:03:46

Another possibility is:


SELECT Client, Date, Value
FROM tb AS A
WHERE IDKey = (
    SELECT MIN(IDKey)
    FROM tb AS B
    WHERE Date = (
        SELECT MAX(Date)
        FROM tb AS C
        WHERE B.Client = C.Client
      )
      AND A.Client = B.Client
  )

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2003-03-07 : 12:36:47

Thanks! I see it. But what if I need all of the records from the parent table regardless of whether child records exist? Doesn't the WHERE negate a LEFT OUTER JOIN?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources