select top 2 per group? - 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)

select top 2 per group?

Author

Topic

Griz
Starting Member

3 Posts

Posted - 2008-05-24 : 09:35:47

I have been struggling with this one for the past week and I’m at my wits end. Please help!

I have a log table that I need to find all transactions where the transaction type is “Update Item” and the previous entry for the same serial number so that I can show what the item was update from and to. The table looks like this:

Trx_Log
--------------
Date
Trx_type
Serial_num
Condition
Owner

I think I need to use Top 2 in a sub-query somehow but I’m not sure of the proper syntax.

Any help you can offer will be appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-24 : 10:56:43

SELECT *
FROM
(
SELECT *
FROM Trx_Log
WHERE Trx_type ='Update Item'

UNION ALL

SELECT t.*
FROM Trx_Log t
INNER JOIN
(SELECT t2.Serial_num,MAX(t2.Date) as MaxDate
FROM Trx_Log t1
INNER JOIN Trx_Log t2
ON t2.Serial_num=t1.Serial_num
WHERE t1.Trx_type ='Update Item'
AND t2.Date <t1.Date
GROUP BY t2.t2.Serial_num)max_row
ON max_row.Serial_num=t.Serial_num
AND max_row.MaxDate=t.Date
)t

Griz
Starting Member

3 Posts

Posted - 2008-05-25 : 09:19:39

Thank you for your quick reply.

Your code looks like the answer I've been looking for but for some reason I get the error "Could not find C:\trx_log.mdb". I seems to be looking for a database named trx_log instead of a table in the current database.

Any suggestions?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-25 : 09:54:00

quote:
Originally posted by Griz

Thank you for your quick reply.

Your code looks like the answer I've been looking for but for some reason I get the error "Could not find C:\trx_log.mdb". I seems to be looking for a database named trx_log instead of a table in the current database.

Any suggestions?

Ah i see why. There's a small typo in the query posted. Please modify it like this.

SELECT *
FROM
(
SELECT *
FROM Trx_Log
WHERE Trx_type ='Update Item'

UNION ALL

SELECT t.*
FROM Trx_Log t
INNER JOIN
(SELECT t2.Serial_num,MAX(t2.Date) as MaxDate
FROM Trx_Log t1
INNER JOIN Trx_Log t2
ON t2.Serial_num=t1.Serial_num
WHERE t1.Trx_type ='Update Item'
AND t2.Date <t1.Date
GROUP BY ~~t2.~~t2.Serial_num)max_row
ON max_row.Serial_num=t.Serial_num
AND max_row.MaxDate=t.Date
)t

Griz
Starting Member

3 Posts

Posted - 2008-05-25 : 12:34:49

You are awesome!! I've posted this question on several sites and you are the only one that understood what I needed to do.

The real problem is me. I posted on the wrong site. I'm accually using access 2007. I think that's why the code is acting funny. Do you know how to do the same thing in access?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-25 : 13:24:48

quote:
Originally posted by Griz

You are awesome!! I've posted this question on several sites and you are the only one that understood what I needed to do.

The real problem is me. I posted on the wrong site. I'm accually using access 2007. I think that's why the code is acting funny. Do you know how to do the same thing in access?

Thanks

. I've not worked in Access but can certainly provide you a link

http://www.blueclaw-db.com/accessquerysql/

I have had a look and it seems like you dont have derived tables in access. What you could do is to define a table that holds the result of query

SELECT t2.Serial_num,MAX(t2.Date) as MaxDate
FROM Trx_Log t1
INNER JOIN Trx_Log t2
ON t2.Serial_num=t1.Serial_num
WHERE t1.Trx_type ='Update Item'
AND t2.Date <t1.Date
GROUP BY t2.t2.Serial_num

and then take inner join with it in main query

SELECT *
FROM Trx_Log
WHERE Trx_type ='Update Item'

UNION ALL

SELECT t.*
FROM Trx_Log t
INNER JOIN YourCreatedTable max_row
ON max_row.Serial_num=t.Serial_num
AND max_row.MaxDate=t.Date

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources